CODEWITHSUNDEEP
vbafor-loopformattingrange
not_a_comp_scientist
not_a_comp_scientist

Reputation: 348

Running a For Loop in VBA on a Selected Range

What I am trying to do is simple but I just can't get it to work. I want select a range of cells and the run a macro that will:

  1. Run a for loop from the first row to the last row on the range selected
  2. Then return the "=NUMBERVALUE()" of the concatenated values in each column of the row
  3. The output column will be right next to the last column of the selected range

This is what I have for the code:

Dim myRange As Range
Set myRange = Selection

First_Col = myRange.Column
Last_Row = myRange.Rows.Count + myRange.Row - 1
Last_Col = myRange.Column + myRange.Columns.Count - 1
Out_put_Col = Last_Col + 1


For i = myRange.Row To Last_Row

    Cells(i, Out_put_Col) = "=NUMBERVALUE(Cells(i, First_Col) & Cells(i, First_Col+1)"

Next i

VBA is having issues with what is written in the for loop. I am newer to VBA but based on the way this is written I would have though it would work. Thanks for the help.

Upvotes: 0

Views: 284

Answers (1)

Tim Williams
Tim Williams

Reputation: 166316

more like:

For i = myRange.Row To Last_Row

    Cells(i, Out_put_Col) = "=NUMBERVALUE(" & Cells(i, First_Col).address & ":" & _
                                          Cells(i, First_Col+1).address & ")"

Next i

I'm not sure NumberValue works like that though.

Upvotes: 1

Related Questions