0

I have a range that is B3:B500 I want to change the value in each cell in that range The range however, is dynamic so I need to look from B3 to last row

First: How do I get it to change the range to work to last row rather than preset range?

Second: I want to change each individual cell value to something like this:

myCell.Value = "=" & Chr(34) & Chr(61) & myCell.Value & Chr(34)

How do I get it to go through cell by cell to make change to each cell in the "dynamic" range we just created?

Appreciate all the help I can get... pretty new at VBA so please bear that in mind and keep it simple :)

I.e. Cell b3 contains: "ASP" (Text only) Change to: ="="ASP" (formula instead giving result =ASP)

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Jay
  • 47
  • 2
  • 12
  • what code have you *tried*? Or even looked for? [Looping over a range](http://stackoverflow.com/questions/3875415/loop-through-each-cell-in-a-range-of-cells-when-given-a-range-object), [Methods for getting the last row](http://stackoverflow.com/questions/71180/how-can-i-find-last-row-that-contains-data-in-the-excel-sheet-with-a-macro) – Wolfie Mar 17 '17 at 08:19
  • Hi, Yes thank you I have looked at them, but I am very new to VBA don't really understand what all that means. I would appreciate a bit of explaination as to what each section is doing... sorry for the inconvinience. – Jay Mar 17 '17 at 08:39

1 Answers1

0
Dim lastRow As Integer: lastRow = 3
Dim myCell As Range
'determine last row of current region
Do While(Not IsEmpty(Cells(lastRow + 1, 2)))
    lastRow = lastRow + 1
Loop
'loop through the range
For Each myCell In Range(Cells(2, 2), Cells(lastRow, 2))
    myCell.Value = "=" & Chr(34) & Chr(61) & myCell.Value & Chr(34)
Next myCell

In this code, first you determine last row using While loop, which loops until the last row in column is found. Then use For Each loop, where you loop through the range, from third row to last row in B column.

Michał Turczyn
  • 32,028
  • 14
  • 47
  • 69
  • `CurrentRegion` seems like an odd choice here? – Wolfie Mar 17 '17 at 08:20
  • Thank you for this, seems however that it adds the works on column C rather than B? Do I need to set range to b3? – Jay Mar 17 '17 at 08:31
  • Sory for that, my mistake. Should work as desired now. – Michał Turczyn Mar 17 '17 at 08:48
  • Works amazingly... thanks a million! so, to understand... For Each myCell In Range(Cells(2, 2), Cells(lastRow, 2)) ---> lastRow,"2" and Cells(2, "2" --> the 2s in "" refer to the column number from the sheet start... i.e. 2 here is column 2 = column B ? Sorry about this, just trying to learn more about VBA – Jay Mar 17 '17 at 08:50
  • Generally: `Cells(rowIndex, columnIndex)`, both parameters are integers, specyfying row and column. For rows it's easy, for columns: 1 = A, 2 = B, etc., just like you expected. – Michał Turczyn Mar 17 '17 at 09:02
  • 1
    It's worth noting that the `columnIndex` can be a string for the column, so you can use `ActiveSheet.Cells(4, "B")` for Cell "B4" – Wolfie Mar 17 '17 at 09:26
  • Fantastic, thank you all for the great help... works great and understand a bit better VBA, think I need to continue learning :) – Jay Mar 17 '17 at 11:24