hasformula - hasformula() - .hasformula or ???

[littlepete]

hello ...

it's a pity that internet gives so much wrong answers...
so this is my question:
if i want to go through all cells in a row one by one and skip the cells that have a formula, should it be:
If Range(nieuwerij & dezekolom).HasFormula() = True Then GoTo next
(nieuwerij=new row; dezekolom = this column)

at the start i put dim 's for both names and in the rest of the vba they work well... so why is it not working???

thank you again !!!

 

[Joe4]

The best place to go is the source, Microsoft: Range.HasFormula property (Excel)

I suspect the issue may be with how you are building your range. Is "dezekolom" a letter or number?
"Range" does not like numbers for column references.
Also, it looks backwards to me, as it is usually Column first, then Row when using Range.
Cells usese the other way around, and can accept letters or number for the column reference, i.e.

If Cells(nieuwerij, dezekolom).HasFormula = True Then GoTo next

Though I am not a big fan of using "GoTo" statements. I typically only use them when using error handling.
They usually aren't necessary if you structure your code correctly.

 

[littlepete]

The best place to go is the source, Microsoft: Range.HasFormula property (Excel)

I suspect the issue may be with how you are building your range. Is "dezekolom" a letter or number?
"Range" does not like numbers for column references.
Also, it looks backwards to me, as it is usually Column first, then Row when using Range.
Cells usese the other way around, and can accept letters or number for the column reference, i.e.

If Cells(nieuwerij, dezekolom).HasFormula = True Then GoTo next

Though I am not a big fan of using "GoTo" statements. I typically only use them when using error handling.
They usually aren't necessary if you structure your code correctly.

thanks for your answer:

1. i know range does the opposite of cells: range(column, row) and cells(row, column) and look that IS what i did !
2. just exactly to avoid all that dim troubles my dezekolom en nieuwerij are not range, not integer but variant
i learned that should be ok all the time?

3. if goto is not ok how can i skip a part of my macro to go to the next cell in the active row,
part that starts with " volgende: " (next)

so please give me the fish, my name is hunter i dont want to learn fishing tonight...

 

[Joe4]

1. i know range does the opposite of cells: range(column, row) and cells(row, column) and look that IS what i did !

You have:
Range(nieuwerij & dezekolom)
where you said:
(nieuwerij=new row; dezekolom = this column)
So you essentially have:
Range(row & column)
which is backwards.

2. just exactly to avoid all that dim troubles my dezekolom en nieuwerij are not range, not integer but variant

What are the exact values of dezekolon and nieuwerij in this case?
If you aren't sure, you can use a MsgBox to return their values, i.e.
MsgBox dezekolom
MsgBox nieuwerij

Also, using the Variant type is typically not a good idea, as it allows anything. So that doesn't help prevent you from using the wrong values.
When it comes to rows and columns, if you want numbers, use Long, which is more than sufficient for any row or column number on a sheet.
If you want column letters, then use String for that one.

3. if goto is not ok how can i skip a part of my macro to go to the next cell in the active row,

Without seeing the rest of your code, I cannot tell you exactly (it really depends on the structure of the data you are working with and what you are trying to do).
But typically you use loops, and then IF statements to check if they meet certain criteria,and perform whatever operations you want only when those conditions are met.

 

[littlepete]

" If Cells(nieuwerij, dezekolom).HasFormula = True Then GoTo next "
I did write range in post nr. 1 here but that was my mistake, my macro says range...

i will try change variant to long... both names are numbers, i'm already checking with a msgbox
to verify which row and column are checked...
remains the question how the skip cells with a formula...
thanks for your patience !!!

 

[Joe4]

You can use "HasFormula" to skip cells with a formula.
But this IF statement must be contained within a bigger loop, to see which ranges you are looping through.

Have you written that part yet? If so, please post it.
If not, please explain how we can determine which range to apply this to.

Basically, we need to determine the following:
1. What range do you want to apply this logic too?
2. How can we identify if a cell has a formula or not (we can do that with "HasFormula)?
3. Exactly what you want to happen when a cell does or does not have a formula?

If you answer questions 1 and 3 above (since we already have the answer to question 2), we can probably help you come up with the code that you need.

 

[littlepete]

hello ...

good news on the end of the day (almost):

If Cells(nieuwerij, dezekolom).HasFormula = True Then GoTo volgende
is working after changing nieuwerij & dezekolom to long
also figured out to skip without deleting not to change values with :
If Len(keuze) > 0 Then
Cells(nieuwerij, dezekolom).Value = keuze
End If

this way formulae are skipped, and cells with values that don't need to be changed
that way i can loop through the entire row and adjust where needed be

i'd like to thank you it's another little step for mankind hahaha
thanks !!!

 

[Joe4]

You are welcome.

Glad you got things working the way you need!