CODEWITHSUNDEEP
excelvbaformulavlookup
Gokuba
Gokuba

Reputation: 13

VBa vlookup , replace range as variable

OK I will reformulate the question.

I have a worksheet with a tab named"My INT". This tab contains a data table and a button with an assigned macro called "importRMR". code below:

Sub importRMR()
Dim rng As Range

Set rng = ActiveSheet.Range("G3")
Sheets.Add(After:=ActiveSheet).Name = "RMR " & Format(Date, "DD-MM-YY")
ActiveSheet.Buttons.Add(966.75, 27.75, 153.75, 125.25).Select
Selection.OnAction = "Cimp"

Selection.Characters.Text = "Importuj"
With Selection.Characters(Start:=1, Length:=13).Font
    .Name = "Tahoma"
    .FontStyle = "Standaard"
    .Size = 16
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    End With
End Sub

This creates a tab with a specific name :RMR " & Format(Date, "DD-MM-YY")

NOw I manually paste the table( always identical ) to the newly created tab "RMR 03/08/2018". the next step is to click the button in this tab ( Importuj) and the result I am aiming for is to : 1. Vlookup data in tab "my INT" where the range( lookup array) is tab "RMR" and then delete the rmr tab.

such procedures like triming the data pasting values and so on I am able to do, just the vlookup but is a problem.

my current code for "importuj " button is: ub TEST()

Dim DOTR As String

Dim shT As String
Set shT = Sheets(DOTR).Range("E2:H584")




'shT = Sheets(DOTR).Range("c1:e2").Select






DOTR = "RMR " & Format(Date, "DD-MM-YY")
'Sheets(DOTR).Range ("E2:H584").selc


Worksheets("My INT").Range("N3").Formula = "=vlookup(c3,sht,3,0)"


End Sub

Unfortunately, I get an error - "Compile Error" - Object required.

Upvotes: 0

Views: 992

Answers (1)

ProfoundlyOblivious
ProfoundlyOblivious

Reputation: 1485

The goal is to combine several different strings into a one cohesive string:

=VLOOKUP(C3,'ABCDEF 03-08-18'!$B$4:$D$10,3,0)

That will be placed inside a cell using:

Worksheet.Range.Formula

The worksheet name "ABCDEF" is arbitrary and we create the date on the fly. We use a named range in Excel "myNamedRange" and reference the address property to allow flexibility without editing code.

Dim strSheetName As String
Dim strNamedRange As String
Dim strDateSegment As String
Dim strPrefix As String
Dim shT As String

strSheetName = "My INT"
strNamedRange = "myNamedRange"
strDateSegment = Format(Day(Date), "00") & "-" & Format(Month(Date), "00") & "-" & Right(Year(Date), 2) & "'!"
strPrefix = "'" & "ABCDEF"

shT = "=VLOOKUP(C3," & strPrefix & strDateSegment & Worksheets(strSheetName).Range(strNamedRange).Address & ",3,0)"

Worksheets("My INT").Range("N3").Formula = shT

Upvotes: 2

Related Questions