Parse column number to letters

[Flyingmeatball]

I need to write a simple formula that will reference every 7th column on another worksheet. For example: my first cell should be something like =Otherpage!C1. The next sell will be =Otherpage!J1. I'd like to write a macro that outputs these formulas as a string, like "=Otherpage" & colnum(X) & "1". Does anyone know how I parse something like "colnum(x)" to "BA" column labels? I need the physical letter name.

Thanks!

 

[Norie]

Actually, you might not need the column letter.

If you were to create/write the formulas using R1C1 notation then you could use the column number.

 

[Flyingmeatball]

Well getting the column letter is the end goal. I can write a macro if i wanted just to move the column, but the point is to write a macro that will output "C, J Q" etc. Im writing this formula in another proprietary spreadsheet program, but it has a lot of the same formulas. I need my ending formula to enter into a physical cell to be "=somesheet!C1", then the next should be 7 columns away, so i need to parse a change of 7 columns into a change from C to J. Make sense?

Thanks!

 

[PaddyD]

"... so i need to parse a change of 7 columns into a change from C to J."

not necessarily. In formula-land:

=INDEX($C$1:$AA$1,,COLUMN($A$1)+((COLUMN()-1)*7))

...in colA and copied across would return the values from c1, j1,q1 etc

 

[Flyingmeatball]

That may indeed work, but I need it to be much cleaner. I need my excel formula to be a simple cell reference. This will be going to a client, and they need to be able to trace the calc, which I'm pretty sure they won't be able to do with your formula. Here's the code I've been working with to make it an output...I just need to fill in that one line.

Sub parse()

Dim formulacol As Integer
Dim x As Integer
Dim output As String
Dim currentparse As String

x = 3


For formulacol = 1 To 50

'Parse integer x to currentparse (current column label)

output.Value = "=othersheet!" & currentparse & "1"
Cells(1, formulacol).Value = output

x = x + 7

Next formulacol

End Sub

thanks for your help!

 

[PaddyD]

vba's not my thing, but I expect the form of the solution to be as above for the formula approach - i.e. iterate by position, not letter - then get the reference in the format you require by embedding the iterations in an address() or similar.

 

[pgc01]

Hi

Not sure I understood correctly but I think you want something like this:

Sub parse()
Dim formulacol As Long
Dim x As Long
 
x = 3
For formulacol = 1 To 50
    Cells(1, formulacol).Value = Worksheets("OtherSheet").Cells(1, x).Value
    x = x + 7
Next formulacol
End Sub

 

[Flyingmeatball]

Not quite, that formula I believe gives me the values stored in those cells. Basically, all i want is the letter label of every 7th column from A to IV. I want my outputs to go, C, J, Q, X, AE and so on.

 

[pgc01]

Ok.

This code writes every seventh column label starting from "C" in the first row of the current worksheet.

Since you said until IV I assumed you have a version of excel with 256 columns.

Sub parse()
Dim formulacol As Long
Dim x As Long
 
formulacol = 1
For x = 3 To 256 Step 7
    Cells(1, formulacol).Value = Split(Cells(1, x).Address, "$")(1)
    formulacol = formulacol + 1
Next x
End Sub

 

[Flyingmeatball]

Perfect! That's exactly what I needed. I'm not familiar with the split function but thats the result i needed. Thanks again!