Relative reference in the use of the formula left VBA

[RicardoCardoso]

I created this macro to count the number of rows starting from column B. Then I created a cycle to fill columns 3,4,5 and 6 with excel formulas. my problem is that the references should be relative and not absolute (b5,b6,b7 and c5, c6,c7...until the last line). I can't finish the macro. Can you help me? Thanks.

Dim lastrow As Long, linha As Integer

lastrow = Range("b5").End(xlDown).Row

For linha = 5 To lastrow
'preenche a coluna dimensao (3)
Cells(linha, 3).Value = "=left(b5,2)*1"
'preenche a coluna tipo (6)
Cells(linha, 6).Value = "=mid(B5,4,4)"
'preenche a coluna 20 (4)
Cells(linha, 4).Value = "=if(c5=20,1,)"
'preenche acoluna 40(5)
Cells(linha, 5).Value = "=if(c5=40,1,)"
MsgBox (linha) & (lastrow)
Next

 

[johnnyL]

Dim lastrow As Long, linha As Integer

lastrow = Range("b5").End(xlDown).Row

For linha = 5 To lastrow
'preenche a coluna dimensao (3)
Cells(linha, 3).Value = "=left(b" & linha & ",2)*1"
'preenche a coluna tipo (6)
Cells(linha, 6).Value = "=mid(B" & linha & ",4,4)"
'preenche a coluna 20 (4)
Cells(linha, 4).Value = "=if(c" & linha & "=20,1,)"
'preenche acoluna 40(5)
Cells(linha, 5).Value = "=if(c" & linha & "=40,1,)"
MsgBox (linha) & (lastrow)
Next

 

[Peter_SSs]

Welcome to the MrExcel board!

If you put them all in at once instead of line-by-line, Excel will do the row adjustments for you automatically.
(You can also do all four columns at once)
Try

Dim lastrow As Long

lastrow = Range("B5").End(xlDown).Row
Range("C5:F" & lastrow).Formula = Array("=left(B5,2)*1", "=if(C5=20,1,)", "=if(C5=40,1,)", "=mid(B5,4,4)")

 

[RicardoCardoso]

Dim lastrow As Long, linha As Integer

lastrow = Range("b5").End(xlDown).Row

For linha = 5 To lastrow
'preenche a coluna dimensao (3)
Cells(linha, 3).Value = "=left(b" & linha & ",2)*1"
'preenche a coluna tipo (6)
Cells(linha, 6).Value = "=mid(B" & linha & ",4,4)"
'preenche a coluna 20 (4)
Cells(linha, 4).Value = "=if(c" & linha & "=20,1,)"
'preenche acoluna 40(5)
Cells(linha, 5).Value = "=if(c" & linha & "=40,1,)"
MsgBox (linha) & (lastrow)
Next

Thank you very, very much. I'm curious and I love excel, but I don't have much knowledge of VBA. It works, thank you very much.

 

[johnnyL]

Glad we could help you @RicardoCardoso. The code @Peter_SSs posted is a far more elegant approach to achieve what you wanted.

 

[RicardoCardoso]

Welcome to the MrExcel board!

If you put them all in at once instead of line-by-line, Excel will do the row adjustments for you automatically.
(You can also do all four columns at once)
Try

Dim lastrow As Long

lastrow = Range("B5").End(xlDown).Row
Range("C5:F" & lastrow).Formula = Array("=left(B5,2)*1", "=if(C5=20,1,)", "=if(C5=40,1,)", "=mid(B5,4,4)")

Fantastic! Thank you very much. The presented solution works very well and simplifies the steps. I am impressed with the capabilities of excel and with your knowledge. Thanks.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.