0

I am having trouble trying to figure out how to save a result of a for loop as it goes through each row.

For example if you have A1:A45 filled with values and you want to add 6 to each cell in that range and output the resultant for each operation into column B.

I am just missing the portion that gets the for loop to output into a new column for every operation performed. I think from there I can troubleshoot and google tutorials.

This is a snippet of a macro I am making. After my concatenation operation I would like to save the new string to a new column and as it goes through the loop, the resultant string would save sequentially.

Select Case numCav
    Case Is = 4
    For a = 2 To lastUserDim1
            For cavNum = 1 To 4
                newDimName = .Cells(a, 1).Value2 & "_Cav" & cavNum
                .Cells(a, 8) = newDimName
            Next cavNum
    Next a
JGuy
  • 5
  • 1
  • `.Cells(a, 8) = newDimName` - you're overwriting the same cell inside your `For cavNum = 1 To 4` loop. Not sure what the intended behavior is. – BigBen Oct 29 '20 at 16:05
  • Yes that is correct, I am looking for direction on how to output the values without over writing them. I'm looking for the logic (coding scheme) behind my question so I can understand it better for immediate and future use. The intended behavior was phrased in my question. – JGuy Oct 29 '20 at 16:54
  • I don't understand *where* the values are supposed to be output. What exactly does `output the resultant for each operation into column B` mean? That cells in column B get populated one after another? – BigBen Oct 29 '20 at 16:55
  • Yes exactly, I'm missing that portion. – JGuy Oct 29 '20 at 16:56
  • `.Cells(.Rows.Count, 8).End(xlUp).Offset(1).Value = newDimName` – BigBen Oct 29 '20 at 16:57

1 Answers1

0

If you are just looking to output the newDimName into the next available cell in column 8, then you can use .End(xlUp):

.Cells(.Rows.Count, 8).End(xlUp).Offset(1).Value = newDimName

All this is doing is starting from the very bottom of column 8 (Rows.Count is the number of the last row), then going up (equivalent of Ctrl and ↑), then Offsetting one to the next row.

BigBen
  • 46,229
  • 7
  • 24
  • 40
  • I see, yes that makes sense. Many thanks! I want to be able to understand so I can have this logic in my head for future use. – JGuy Oct 29 '20 at 17:56
  • @JGuy - you may want to see [this question](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba), which is about finding the last cell, and discusses `End(xlUp)` in more detail. – BigBen Oct 29 '20 at 18:00