Need Help with Excel Formula 1

[hrm1220]

I’m not very good at VBA for excel, but seem to muddle through most things. My current issue with VBA is:
I’m trying to reference a workbook in a vlookup formula using VBA. The workbook and worksheet will change every quarter (no naming convention). Here's the following code:

PreviousSheetName = ActiveWorkbook.Name
PreviousSheet = ActiveSheet.Name
msg = MsgBox("Select your approved Tool List", vbOKOnly)
CurrentSheetName = Application.GetOpenFilename("Excel Files *.XLS,*.xls")
Workbooks.Open (CurrentSheetName)
CurrentSheetName = ActiveWorkbook.Name
CurrentSheet = ActiveSheet.Name
Range("A4").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-12],'[" & PreviousSheetName & "]" & PreviousSheet & !P:AA,12,0)"

In Excel it gives me a #NAME? error, but if I click into the cell it will actually give me a value.

Please help. Tell me what I’m doing wrong.

Thanks,

Heidi

 

[macropod]

hi Heidi,

The only thing I can see wrong with your code is a missing quote mark:
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-12],'[" & PreviousSheetName & "]" & PreviousSheet & !P:AA,12,0)"
should be:
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-12],'[" & PreviousSheetName & "]" & PreviousSheet & "!P:AA,12,0)"
(extra quote mark near end)

However, without the extra quote mark, your code shouldn't even run ...


Cheers

 

[FaneDuru]

The working code should be:

ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-12],'[" & PreviousSheetName & "]" & PreviousSheet & "'!P:AA,12,0)"

See the cote mark ' previous "!P:...."

Fane Duru

 

[hrm1220]

Thanks for the inputs.

Even after I add the "' I still get a #Name? error. I think that the PreviousSheetName and Previoussheet need to be defined, but when I define them as strings it still gives me #Name? error. And again when I click in the cell of Excel, the formula then works.

 

[macropod]

Hi Heidi,

Ahh, but of course -
You've got mixed references, using R1C1 and normal referencing! That's what's causing the problem.

What you need is something like:
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-12],[" & PreviousSheetName & "]" & PreviousSheet & "!C16:C27,12,0)"

Cheers

 

[SkipVought]

Hi,

I absolutely hate and NEVER use R1C1 notation.

Yet another way to skin a cat...

    With Range("A4")
        .Formula = _
        "=VLOOKUP(" & Cells(.Row, .Column - 12).Address & _
        ",'[" & PreviousSheetName & "]" & PreviousSheet & "'!P:AA,12,0)"
    End With

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[hrm1220]

Thanks for your help. I tried the formula below:
With Range("A4")
.Formula = _
"=VLOOKUP(" & Cells(.Row, .Column - 12).Address & _
",'[" & PreviousSheetName & "]" & PreviousSheet & "'!P:AA,12,0)"
End With
It works, but now it only looks a cell "$P$5" when I need it to look at the active row in column P. I've tried taking the address out and get a syntax error. Is there another choice in "Cells(.Row,.Column-12).Address"?

 

[SkipVought]

Look at HELP on the Address property. There are 2 arguments that control the absolute reference indicator for rows and columns.


Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[Goppi]

Heidi, Which value do you try to search for with vlookup - what should the particular cell in column P have as an example - a value like "$A$1" or the value of the cell A1?

Gop

 

[hrm1220]

the value should look like P5 not $P$5.

 

[hrm1220]

Thanks for your help on the Address Property. It works now. The formula looks like this:

With Range("A4")
.Formula = _
"=VLOOKUP(" & Cells(.Row, .Column - 12).Address(rowaboslute,columnabsolute) & _
",'[" & PreviousSheetName & "]" & PreviousSheet & "'!P:AA,12,0)"
End With

 

[RPrinceton]

Hi Everone,
I have an Excel worksheet that I want to conditionally apply
the 'PROPER' function to. The code below results in an Excel error that indicates my reference is circular, which I do not understand since I am bumping the value of r on each iteration. How can I conditionally apply a formula to a specific cell.
Please advise. Thx in advance.
Regards,
RPrinceton

code snippet:
Do While r <> lastrow ' Loop until end of rows
c = 1
r = r + 1
With Worksheets(1).Cells(r, c)
addr = .Address(RowAbsolute:=False) 'get addr in $A1 format
addr = Mid(addr, 2) ' trim down to A1, A2 etc
if .value > "t"
.Formula = "=PROPER(" & addr & ")"
End With
Loop

 

[SkipVought]

Please post in a NEW THREAD.

Skip,
[sub]
[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine![/sub]