0

This is kind of a follow up question to my previous question but I am trying to make a code a little more dynamic. As I am looping through a row, I am trying to use ActiveCell.Column to get the active column for a particular instance. However, after printing out what the ActiveCell is, it is not going along the row correctly, like the loop is. Any ideas as to why this might be happening?

Code: 

Set rw = Range("A5:CG5")
myCol = ActiveCell.Column


For Each row In rw.Rows
    For Each cell In row.Cells
        Select Case cell.Value

        Case "2"
            ThisWorkbook.Worksheets("Sheet1").Range("E27:E51").Copy Destination:=Sheets("Sheet2").Cells(31, myCol)

        Case "61"
            ThisWorkbook.Worksheets("Sheet1").Range("U36:U51").Copy Destination:=Sheets("Sheet2").Cells(40, myCol)

        End Select
    Next cell
Next row

Where myCol is the activecell.column but when printing it out it either skips columns all together or stays on the same column the entire time.

unclass123
  • 37
  • 1
  • 7
  • 1
    Are you sure that cells are storing text values? In other words: `2` is not equal to `"2"`. – Maciej Los Aug 11 '17 at 18:32
  • Also, you declare `sh`, but never use it. Is the `rw` range the activesheet? – BruceWayne Aug 11 '17 at 18:35
  • @MaciejLos Actually, no I think they are storing actual integers. does that matter? – unclass123 Aug 11 '17 at 19:29
  • @BruceWayne yeah I just realized that, I'll take it out – unclass123 Aug 11 '17 at 19:30
  • You are starting to print .Range("E27:E51") on Row 31 and .Range("U36:U51") on Row 40, isn't it what is happening? And the Column is the one that you are Selecting the moment the code run, thats why you should Refer to this so you avoid using`.Select/.Activate/Selection/Activecell/Activesheet/Activewor‌​kbook` https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba – danieltakeshi Aug 11 '17 at 20:17

0 Answers0