asked on

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

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

ASKER CERTIFIED SOLUTION

Norie

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Andreas Hermle

ASKER

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

Rob Henson

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 Hermle

ASKER

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 Hermle

ASKER

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.