Help with Vlookup

[Les Stout]

Hi all,
I have one workbook that i open and then make a variable
activeWorkbook.Name and want to use the variable name in the formula,
but it does not like it, could you tell me what i am doing wrong ?
ActiveCell.Offset(0, 11).FormulaR1C1 =
"=VLOOKUP(RC[-11],SuppFileNameC!A:N,12,0)"

i have the following code that i got from this site and now i need to
put in some vlookups, is it possible ?

Sub InsertCalcS()
Dim rng As Range
Dim Lrow As Long
Dim CalcMode As Long
Const col As String = "I" '<<== CHANGE

Lrow = Cells(Rows.Count, col).End(xlUp).Row

On Error GoTo XIT

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

Set rng = Range(col & "4:" & col & Lrow) '<=== From row 4
With rng
.FormulaR1C1 = "=SUM(RC[-2]-RC[-1])" '<<== CHANGE
.Offset(0, 1).FormulaR1C1 = _
"=SUM(RC[-1]*RC[-5])" '<<== CHANGE
End With

XIT:
With Application
.Calculation = CalcMode
.ScreenUpdating = True
End With
TotalsS
End Sub

Les Stout

 

[Jim Cone]

Les,
Assuming "strName" is the variable and the actual sheet name
is: "SuppFileNameC".
Then using all R1C1 references, something very close to the following worked for me...

Dim strName As String
strName = "'SuppFileNameC'"
ActiveCell.Offset(0, 11).FormulaR1C1 = "=VLOOKUP(RC[-11]," & strName & "!C1:C14,12,0)"

Regards,
Jim Cone
San Francisco, USA


"Les Stout" <[email protected]>
wrote in message
Hi all,
I have one workbook that i open and then make a variable
activeWorkbook.Name and want to use the variable name in the formula,
but it does not like it, could you tell me what i am doing wrong ?
ActiveCell.Offset(0, 11).FormulaR1C1 =
"=VLOOKUP(RC[-11],SuppFileNameC!A:N,12,0)"

i have the following code that i got from this site and now i need to
put in some vlookups, is it possible ?
- snip -
Les Stout

 

[Dave Peterson]

I'd let excel do the work for me.

But first, do you mean that that other worksheet is in a different workbook or
is a worksheet in the same workbook?

Option Explicit
Sub testme()

Dim myLookUpRng As Range

With Workbooks("book2.xls").Worksheets("SuppFileNameC")
Set myLookUpRng = .Range("a:n")
End With

ActiveCell.Offset(0, 11).FormulaR1C1 _
= "=VLOOKUP(RC[-11]," _
& myLookUpRng.Address(external:=True, ReferenceStyle:=xlR1C1) _
& ",12,0)"
End Sub

I guess it really doesn't matter where that worksheet is. As long as you define
that myLookupRng correctly. It can be in the same workbook--or a different
workbook (as long as it's already open).

Hi all,
I have one workbook that i open and then make a variable
activeWorkbook.Name and want to use the variable name in the formula,
but it does not like it, could you tell me what i am doing wrong ?
ActiveCell.Offset(0, 11).FormulaR1C1 =
"=VLOOKUP(RC[-11],SuppFileNameC!A:N,12,0)"

i have the following code that i got from this site and now i need to
put in some vlookups, is it possible ?

Sub InsertCalcS()
Dim rng As Range
Dim Lrow As Long
Dim CalcMode As Long
Const col As String = "I" '<<== CHANGE

Lrow = Cells(Rows.Count, col).End(xlUp).Row

On Error GoTo XIT

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

Set rng = Range(col & "4:" & col & Lrow) '<=== From row 4
With rng
.FormulaR1C1 = "=SUM(RC[-2]-RC[-1])" '<<== CHANGE
.Offset(0, 1).FormulaR1C1 = _
"=SUM(RC[-1]*RC[-5])" '<<== CHANGE
End With

XIT:
With Application
.Calculation = CalcMode
.ScreenUpdating = True
End With
TotalsS
End Sub

Les Stout

 

[Les Stout]

Thanks Dave, have tried and it works great.

Les Stout