Relative reference in the use of the formula left VBA

RicardoCardoso

New Member
Joined
Jan 3, 2022
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
VBA Code:
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
 
Upvote 0
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

VBA Code:
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)")
 
Upvote 0
Solution
VBA Code:
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.
 
Upvote 0
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

VBA Code:
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.
 
Upvote 0
You're welcome. Thanks for the follow-up. :)
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,296
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top