Vlookup driving me nuts

[garden_rael]

HELLO GUYS... THERE IS SOMETHING SO SIMPLE SO SIMPLE THAT IS DRIVING ME CRAZY...

Ler's see If I can explain my-self...

I'm trying to create a summary for a report that runs almost daily... the summary should go into each created book and look for specific data for an specific user...

I though of doing a simple table, user in one column, dates on the upper row...
Now... the reports doesn't have the same name every day... The name it's... let's say... "Cool Report 1" + Today's Date... (That's how the report it's created every day on a folder), It has the same sheets so no worries on the sheet name...

Now... I was thinking of vlookup, but I didn't want to write single vlookup for dates that won't even exist, I know that might be somehow easier... But I like it the hard way hahaha lol and with a little help of you guys I'm sure I'll make it...

I tried ADDRESS, but when I use it for vlookup it brings up VALUE#! bc it takes my ADDRESS as text,

So... I tried a macro

Dim DD As String
DD = Cells(6, 3).Value

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC2," & DD & ",1,0)"
Range("C9").Select
End Sub

Whenever you want to update a date, It would writte that formula, but my variable DD, It's not written with the macro!!!!!

and I get #NAME?

DD will be set every time you run it with a mobile cell.. for ex..

If you are on cell C8, the values you'd need would be VLOOKUP(B8,DD*which would be "C6"*,3,0), if you go to D8, then It would be
VLOOKUP(B8,DD*D6 in this case*,3,0)...

So the Path info it's in the row 6, and the users ID is in the column D

OMG, I hope someone out there understands this mess...

Hope you can help!

 

[xenou]

Maybe Add:

Dim [COLOR="RoyalBlue"]myFormulaString [/COLOR]as String
[COLOR="RoyalBlue"]myFormulaString [/COLOR]= "=VLOOKUP(RC2," & [COLOR="Red"]DD [/COLOR]& ",1,0)"
ActiveCell.FormulaR1C1 = [COLOR="RoyalBlue"]myFormulaString [/COLOR][COLOR="SeaGreen"]'//this is the whole vlookup formula now[/COLOR]

You might be able to use INDIRECT() in some fashion if you want to get the value in another workbook that you know the name of ... but the other workbook must be open for INDIRECT() to work that way. You can search of INDIRECT.EXT for a way around this if you are interested further.

Alex

 

[garden_rael]

Can anybody understand why this has an error?

Dim AA, CO As String
Dim CC As Variant
DD = Cells(6, 3).Value
AA = "=VLOOKUP(RC2,"
CO = ",1,0)"
ActiveCell.FormulaR1C1 = AA + "'[Call Gaps Portfolio 2009-06-16.xls]Pivot'!R11C12" + CO

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC2," + DD + ",1,0)"
Range("C9").Select

 

[garden_rael]

I'll try that Alex

Let's see how it works

 

[garden_rael]

Alex, It Didn't Work

I used this code:

Dim MYFORMULA As String
Dim DD As String
DD = Cells(6, 3).Value
MYFORMULA = "=VLOOKUP(rc2," & DD & ",1,0)"
ActiveCell.FormulaR1C1 = MYFORMULA


I'm still getting an error, The problems seems to be the address that I direct the vlookup to search, if you put anything different there, it works

 

[xenou]

Dim MYFORMULA As String
Dim DD As String
DD = Cells(6, 3).Value
MYFORMULA = "=VLOOKUP(rc2," & DD & ",1,0)"
ActiveCell.FormulaR1C1 = MYFORMULA
[COLOR="Blue"]MsgBox MYFORMULA[/COLOR]  [COLOR="SeaGreen"]'//view in message box[/COLOR]
[COLOR="blue"]Debug.Print MYFORMULA [/COLOR] [COLOR="SeaGreen"]'//view in immediate window[/COLOR]

What is the formula?

EDIT:
What's in 6,3 anyway...are we mixing R1C1 and Normal Cell Notation?

 

[garden_rael]

I have the path there '[Cool Report 1.xls]Sheet1'!$A:$C

C4 has Date
C5 has "[Call Gaps Portfolio "&TEXT(C4,"YYYY-MM-DD")&".xls]Pivot"
C6 has =+ADDRESS(7,4,,,C5)&":"$J$46"

 

[xenou]

C6 has =+ADDRESS(7,4,,,C5)&":"$J$46"

Not sure but if you are mixing R1C1 and Standard Notation in the same formula it would probably bust.
In other words, what does =+ADDRESS(7,4,,,C5)&":"$J$46" evaluate to?

This would be bad:
=Vlookup(rc2,$J$1:$J$46,1,0)

 

[garden_rael]

I changed it a little bit, and now works like charm...

Here's the code


Dim a As String
Dim i, fechita As String, cebra As String
Dim renglon, columna As Integer
Application.ScreenUpdating = False
renglon = ActiveCell.Row
columna = ActiveCell.Column
i = Cells(4, 4).Value
fechita = Cells(renglon - 6, columna).Value
If FileFolderExists("U:\SACC\Reportes SACC\Reportes Bilingue\Call Gaps\Call Gaps Portfolio " & fechita + ".xls") Then
Workbooks.Open ("U:\SACC\Reportes SACC\Reportes Bilingue\Call Gaps\Call Gaps Portfolio " + fechita + ".xls")
Windows("Call Gap Tracker (version 2).xls").Activate
cebra = "[Call Gaps Portfolio " + fechita + ".xls]Pivot"
ActiveCell.FormulaR1C1 = _
"=vlookup(RC2,INDIRECT(ADDRESS(7,4,,,""" & cebra & """)&"":""&ADDRESS(46,10,,,)),7,0)"
'Cells(5, 5).FormulaR1C1 = _
"=a"
ActiveCell.Copy
Range(Cells(renglon + 1, columna), Cells(renglon + 32, columna)).PasteSpecial xlPasteFormulas
Range(Cells(renglon, columna), Cells(renglon + 32, columna)).Copy
Range(Cells(renglon, columna), Cells(renglon + 32, columna)).PasteSpecial xlPasteValuesAndNumberFormats
Application.CutCopyMode = False
Cells(renglon, columna).Activate
Windows("Call Gaps Portfolio " + fechita + ".xls").Close

Else
z = MsgBox("Call Gap no existe para fecha dada", vbOKOnly, "Archivo no encontrado!")
Application.ScreenUpdating = True
End If
End Sub


Thanks for all your help Alex!! =)