Retrieve the column number of some cell and then use this number into a formula in a macro code

Andreas Hermle
Andreas Hermle used Ask the Experts™
on
Sub Formula()
Dim Col as Variant

Col = Split(ActiveCell(1).Address(1, -1), "$")(0)
'Retrieve the column number of the cell that is located left from the current cell

ActiveCell.Formula = "=IF(Col & ""2"" ="""","""",""M:\\Katalog\\Bilder_Artikel\\Graphics_JPEGs\\""&MID(Col & ""2"",1,12)&"".jpg"")"
'The Retrieved Column Number is inserted into Formula

The formula in the formula bar should read        =IF(C2="","", etc.
Instead the formula in the formula bar says        =IF(Col & "2" ="","",


End Sub


Dear Experts:

In a macro I try to retrieve the colum number of the cell that is located left from the current cell.
Col = Split(ActiveCell(1).Address(1, -1), "$")(0)

The retrieved column number is then inserted into the following formula:
ActiveCell.Formula = "=IF(Col & ""2"" ="""","""",""M:\\Katalog\\Bilder_Artikel\\Graphics_JPEGs\\""&MID(Col & ""2"",1,12)&"".jpg"")"

The formula in the formula bar should read        =IF(C2="","", etc.
Instead the formula in the formula bar says        =IF(Col & "2" ="","",

What is wrong with this VBA Code?

Help is very much appreciated. Thank you very much in advance.

Regards, Andreas
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Analyst Assistant
Commented:
Andreas

First, you don't need to use Split to get the column number.
Col = ActiveCell.Column -1

Open in new window


Second, if you want to use the column number in a formula you should use R1C1 notation, give this a try.
ActiveCell.FormulaR1C1 = "=IF(RC" & Col & "="""","""",""M:\\Katalog\\Bilder_Artikel\\Graphics_JPEGs\\""&MID(RC" & Col & ",1,12)&"".jpg"")"

Open in new window

Andreas HermleTeam leader

Author

Commented:
great norie, this did the trick. thank youj very much for your quick and professional help. I really appreciate it. regard, andreas
Rob HensonFinance Analyst

Commented:
Your Split statement is getting the Column Letter rather than the column number.

To use that in your original syntax, rather than RC notation as suggested by Norie which does require column number, you are missing a double quote:

Should be:
ActiveCell.Formula = "=IF(" & Col & ""2"" ="""","""",""M:\\Katalog\\Bilder_Artikel\\Graphics_JPEGs\\""&MID(Col & ""2"",1,12)&"".jpg"")"
Andreas HermleTeam leader

Author

Commented:
ups, Rob, I already awarded points. I will test it, but I won't be able to test it before monday morning.

Anyways, thank you veryvery much for your great and professional help. I really highly appreciate it.

Regards, Andreas
Andreas HermleTeam leader

Author

Commented:
Hi Rob, just tried your solution.

I am afraid to tell you that your code throws an error message on ... ""2"" ...
The error says something like ... Compilation error: Expected End of code (or something like that because I got the German Version).

The error message usually pops up if there is a missing double quote or to many double quotes.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial