I would like to write a Macro that would find each non-blank cell in a row, and create a variable of the column number of each, titled something like Cont1, Cont2, etc...
Trouble is, I can't find the command for it to Loop, creating a unique Variable each time. I've come up with the following script so far, which writes 'Cont1' in the column next to the first column number; 'Cont2' next to the second column number, and so on.
Another 'quirk' - which isn't entirely a problem, but I wouldn't mind fixing - is that the .Find command seems to miss the first entry, making it last instead of first.
Ok, here's my script so far:
(I start out by defining the range to be searched - which is accomplished by typing the desired row number in cell A1 on Sheet1.)
Sub SetVariables()
'Auto-update ranges
'
'
Set RefSht = Worksheets("Sheet1")
FromRow = RefSht.Range("A1")
TotCnt = Application.CountA(Refsht.Rows(FromRow))
With Refsht.Rows(FromRow)
_ Set ColN = .Find("*")
_ FirstColN = ColN.Column
_ CntTim = 0
_ For Rep = 1 To TotCnt
_ _ If Not ColN Is Nothing Then
_ _ _ Set ColN = .FindNext(ColN)
_ _ _ CntTim = CntTim + 1
_ _ _ CntVal = "Cont" + CntTim
_ _ _ RefSht.Cells(CntTim + 7, 1) = ColN.Column
_ _ _ RefSht.Cells(CntTim + 7, 2) = CntVal
_ _ Else
_ _ _ Exit For
_ _ End If
_ Next
End With
RefSht.Cells(8, 4) = CntTim
RefSht.Cells(8, 5) = FirstColN
End Sub
FYI, I have the following data entered in Sheet1
A1 = 6
C6 = 600
F6 = 500
H6 = 800
I6 = 500
K6 = 200
L6 = 400
(All the entries in row 6 are just random for testing purposes, as are the final 2 commands - after End With - which I've just been using to keep track of how it's working.)
Again, what I'm looking for is a way to turn each of the 'Cont#' strings into a variable with the value set as the numeric column reference, which could then be use in a series of real-time Looping Macros to be carried out on every non-blank column in succession.
Bookmarks