0

I have a macro that goes the first several rows using a Do Until loop that starts at the top of the column. For this example let that be F1. So the formula for the first 4 rows is =Left($F2,8). Once the loop ends, lets say it ends on row 10. This is now the ActiveCell. I need the formula to now change to =Left(F(ActiveRow'sValue), 4).

My issue is I am not sure how to call upon the Active Row's Value. I will also need the formula to be able to be filled down after. So you can't set the formula to an exact cell, it just needs to know the row and continue.

Please see below code for my attempts.

''''

Sub Adding_Columns()

Dim a As Integer
Dim x As Integer
Dim y As Integer
Dim z As Integer
Dim count As Long

Application.ScreenUpdating = False

a = 0
x = 1
y = 3
z = 2

Sheets(x).Select
Range("A1").EntireColumn.Insert
Range("A2").Select
ActiveCell.Formula = "=Left($B2,4)"
Range("D1").EntireColumn.Insert
Range("D1").EntireColumn.Insert
count = Application.CountA(Range("F2:F250")) - Application.CountIfs(Range("F2:F250"), "*-*")
Range("E2").Select
    Do Until a = count
        ActiveCell.Formula = "=Left($F2,8)"
        ActiveCell.Offset(1, 0).Select
        a = a + 1
    Loop
ActiveCell.Formula = "=Left(" &.Offset(1,0)",4)"

''''

EDIT:

I have used the below formula. Thanks to the pro in the comments :) ActiveCell.Formula = "=Left($F" & ActiveCell.Row & ",4)"

Appreciate all the help in advance.

SneakyKeys1025
  • 33
  • 4
  • 2
    Normally one would [avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). That said, if you need to know a cell's row, you just use `.Row`. And you can concatenate that into a formula. For example: `ActiveCell.Formula = "=Left($F" & ActiveCell.Row & ",8)"`. – BigBen Oct 06 '22 at 18:23
  • Ben you are my hero. I apologize for my poor programming. – SneakyKeys1025 Oct 06 '22 at 18:29
  • No need to apologize, we all were there at one point. – BigBen Oct 06 '22 at 18:30

0 Answers0