Vlookup Formula needs to be updated in different worksheets till lastcolumn which has data

[ankamshetti.nagaraj]

Hi Experts,

I've being working out around to find a solution for my monthly task which i perform every month to retrieve the email received time based on email subject. I've almost done but, there are some issues.

1. I've 12 sheets with the month names respectively.
2. In each and every sheet the Calendar first date start @ Cell E2 i.e, E2 = 1/1/2014, F2 = 1/2/2014 so on a so forth (Excluding weekends).
3. I've a combobox in userform, based on the combobox selected value, that particular sheet gets activated.
3. I've a vlookup formula recorded only for April Sheet from 4/1/2014 through 4/30/2014. When i run the macro it is retrieve the mails of april month and getting populated in the approrpriate sheet.

My issue here is, when i choose a month in the combobox the formula is getting populated with the other month time which is incorrect. Also, i would like to populate the formula till the last column which has the data.

I am using the following code. Any help is really appreciated.

Sub Dump_Data()
Dim lastrow As Long
Dim retval As String

Application.ScreenUpdating = True
'--------------------------------------------------------------------------------------------------------------------
Worksheets("Email Details").Select
lastrow = Range("A" & Rows.Count).End(xlUp).Row

' Filter for required data
Range("A1:G1").Select
Selection.AutoFilter
Range("A1:G" & lastrow).AutoFilter field:=3, Criteria1:="=*PA GMAX*"

Call Copy_Required_Data

Windows("Release Timings - Master Template 2014.xlsx").Activate
'Sheets("Apr'14").Select

Application.ScreenUpdating = False
Sheets(ComboBox1.Value).Visible = True
Application.Goto Sheets(ComboBox1.Value).[E3], True
Application.ScreenUpdating = True

' PA GMAX Release DUMP
Cells.Find(What:="PA GMAX Release", After:=ActiveCell, LookIn:=xlFormulas, _
lookat:=xlPart, Searchorder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, searchformat:=False).Activate
Range("E3").Select

ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R2:C26,'[Release Email Tracker.xlsm]Dump Data'!C5:C7,3,0)"
Windows("Release Timings - Master Template 2014.xlsx").Activate
'Sheets("Apr'14").Select

Application.ScreenUpdating = False
Sheets(ComboBox1.Value).Visible = True
Application.Goto Sheets(ComboBox1.Value).[E3], True
Application.ScreenUpdating = True

Range("E3").Select
Selection.Copy
Range(Selection, Selection.End(xlToRight)).Select
Range("E3:Z3").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Copy

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.Replace What:="PM", Replacement:="AM", lookat:=xlPart, _
Searchorder:=xlByRows, MatchCase:=False, searchformat:=False, _
ReplaceFormat:=False
Application.CutCopyMode = False

[Bernie Deitrick]

I have assumed that your sheet names are formatted mmm'yy, as you showed for April.

Please Login or Register  to view this content.

[ankamshetti.nagaraj]

Hi Bernie Deitrick,

Sorry It could not made my requirement.

I've written a vlookup formula in one of the sheet which has the dates in columns, which will retrieve the data from another workbook which has the dates from 1st to 30 or 31st of the month. Based on the Month selection in the combobox, i want this vlookup needs to be updated from Cell E3 to the end of the column which has the last date of the month.

For Example: If i select the month in Combobox as January, then the formula should be updated from Cell E3 to Cell AA3 (Since, i have excluded the Saturday's and Sunday's), If i select the Month as February the formula should be updated from Cell E3 to Cell X3.. so on and so forth.

This is the formula i am using for the month of April.

ActiveCell.FormulaR1C1 = "=VLOOKUP(R2C5:R2C26,'[Release Email Tracker - Copy.xlsm]Dump Data'!C5:C7,3,0)"

If i select any other month the Range R2C5:R2C26 should be changed accordingly.

Could any one please help me out on this.

[Bernie Deitrick]

You need to post a workbook, showing the formulas that you want for each of the months you talk about.

[ankamshetti.nagaraj]

Hi,

I've attached the files.

Could you please look into this issue.

[ankamshetti.nagaraj]

Hi,

Any update on the above issue. Could someone please revert back to my query. Any Help is really appreciated.

[Bernie Deitrick]

Use this macro: I have assumed that Source Data is always only 2 sheets and that it is open when the macro is run - not sure if those are correct.

Sub TestMacro2()
Dim sht As Worksheet
For Each sht In Workbooks("Output File.xlsx").Worksheets
sht.Range("E3").Resize(1, Application.WorksheetFunction.Count(sht.Range("2:2"))).FormulaR1C1 = _
"=IFERROR(VLOOKUP(R[-1]C,'[Source Data.xlsx]Sheet1'!C1:C3,3,0),VLOOKUP(R[-1]C,'[Source Data.xlsx]Sheet2'!C1:C3,3,0))"
Next sht
End Sub

[ankamshetti.nagaraj]

Hi,

I've tried with the above code and observed that, the formula is getting updated till the last column but not with the exact result in that, all the cells are replacing with "FALSE" text. Moreover, i do not want to update the formula in all the sheets of my template, it should update only one sheet based on the month selection. I've a combobox in a userform, where in when i select the month over there, the vlookup formula needs to be updated in that particular sheet only with the exact result. Also, i would like to get the formula needs to be updated at different rows in the same sheet.

[Bernie Deitrick]

With all your data on sheet1 of Source Data, this will add a new row of formulas to the activesheet

Sub TestMacro2()
Dim sht As Worksheet
Set sht = ActiveSheet
sht.Cells(sht.Rows.Count,"E").End(xlUp)(2).Resize(1, Application.WorksheetFunction.Count(sht.Range("2:2"))).FormulaR1C1 = _
"=VLOOKUP(R2C,'[Source Data.xlsx]Sheet1'!C1:C3,3,0)"
End Sub