LinkBack URL
About LinkBacks
in a vba code, how do I get the letter of a column for which I have the number? (i.e. from previous code I dedermined that my data is in column 15 and 15 is stored in variable mycol)
in a vba code, how do I get the letter of a column for which I have the number? (i.e. from previous code I dedermined that my data is in column 15 and 15 is stored in variable mycol)
Hi
try
You could also use SUBSTITUTE to get rid of the 1
mycol = 15 temp = Cells(1, mycol).Address(rowabsolute:=False, columnabsolute:=False) MsgBox Left(temp, Len(temp) - 1)
MsgBox WorksheetFunction.Substitute(Cells(1, mycol).Address(rowabsolute:=False, columnabsolute:=False), 1, "")
rylo
MsgBox Mid(Cells(1, columnNumber).Address, 2, 2 + CInt(columnNumber < 27))
Your code worked fine and I learned something new today. However it still didn't solve my final problem. I needed to use the referrence to the column in a formula. The formula I'm trying to use looks like:
where colid is my variable column that I determined earlier in the code.For i = 2 To myrows cc = Cells(i, colid).Address(rowabsolute:=False, columnabsolute:=False) ActiveCell.Formula = "=INDEX(Make!A:A,MATCH(""*""" & "Left(Cc, 3)" & """*"",Make!B:B,0))" next i
The formula is suppose to determine the first 3 characters from the string in colid, look for them in column B of Sheet "Make" and return the value from column A, Sheet Make and write it in the active column in sheet1. In column B sheet Make I have something like: "MKJ HGF LBV THD YVX". I can have only 2 or 3 groups of characters separated by space, or I can have any number of groups.
When I try to run the macro I get the message: "application-defined or object -defined error" (error # 1004).
Last edited by VBA Noob; 06-06-2007 at 08:07 AM.
Hi
Try
ActiveCell.Formula = "=INDEX(Make!A:A,MATCH(""*"" & Left(" & cc & ", 3) & ""*"",Make!B:B,0))"
rylo
It worked. Thank you so much! I don't know how I didn't see it!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks