Creating a string using Vlookups in VBA

AyKay11

New Member
Joined
Jul 27, 2012
Messages
6
Good morning,

I have created a userform for people to use to generate a unique reference for a campaign. I use text boxes, option buttons and combo boxes to get the data which is then input into a worksheet and then use a vlookup formula in a worksheet to create the reference. The reference has a unique identifier in it (a countif) so I need this to overwrite with values so that the countif doesn't keep totaling up.

I'm thinking now it would be better if I wrote the vlookup formula in VBA code, so that it would be hard-coded into the worksheet and I don't have to write a copy and paste loop into the code, but I'm having trouble writing this. This is the code I've written as a test so far, but it doesn't seem to like it:


Dim ActDate As String
Dim Name As String
Dim ActName As String
Dim Channel As String
Dim Campaign As String
Dim Product As String
Dim Audience As String
Dim YourCode As Long
Dim ActivityDate As String
Dim ActivityChannel As String

Set ActDate = ComboBox1.Text
Set Name = TextBox2.Text
Set ActName = TextBox3.Text
Set Channel = ComboBox2.Text
Set Campaign = ComboBox3.Text

ActivityDate = ActDate
ActivityChannel = Channel

YourCode = Application.WorksheetFunction.VLookup(ActivityDate, Sheets("Reference Tables").Range("O:P"), 2, False) & "-" & Application.WorksheetFunction.VLookup(Channel, Sheets("Reference Tables").Range("H:J"), 2, False)
MsgBox ("Your Code ") & YourCode

The Countif part isn't in this code yet as I just wanted to make sure it worked first, but I would need a vlookup to create the reference needs to include ActivityDate, Channel, Campaign, Product and Audience and then a Countif for how many times an activity name appears in a column.

I'm not sure where I'm going wrong with this. I have another vlookup that seems to work fine, but for some reason this one doesn't. I'm using a message box so that I can make sure it works first but then it would need to move down a row.

Any and all help always appreciated. Let me know if you need any more detail.
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Saurabhj

Active Member
Joined
Jun 6, 2020
Messages
413
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hi,

Few suggestions to resolve it -
1. What's the result of below formulas ?
msgbox Application.WorksheetFunction.VLookup(ActivityDate, Sheets("Reference Tables").Range("O:P"), 2, False)
msgbox Application.WorksheetFunction.VLookup(Channel, Sheets("Reference Tables").Range("H:J"), 2, False)

2. Activity date is of String type. What is the data type of column O in Reference Tables sheet?

3. What is the result of below code?
MsgBox ("Your Code ") & YourCode

4. Are you getting any error ? if yes on which line?

Thanks,
Saurabh
 

AyKay11

New Member
Joined
Jul 27, 2012
Messages
6
Hi Saurabh,

1. It doesn't get that far as I get "Object Required" relating to Set ActDate = ComboBox1.Text.
If I leave the set of Sets out, I get a 1004 error on the Vlookup.

2. Column O is a date function, which could be an issue as I'm specifying it as a string

3. It never gets that far, I just had that in for it worked.

4. Object Required or 1004 (depending on if I have the Sets active)

I have a feeling my issue could be with the lookup for the date, and that I'm not specifying that correctly for VBA to understand what it's looking for.
 

Saurabhj

Active Member
Joined
Jun 6, 2020
Messages
413
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
- Yes, please check the date format. It would resolve your issue. You can use Format to change the date format.

VBA Code:
Sub dateCheck()
    Dim ActivityDate As String
    ActivityDate = Format(Now(), "DD-MM-YYYY")
   MsgBox ActivityDate
   MsgBox ActiveSheet.Range("A2")   'A2 contains date
   MsgBox ActivityDate = ActiveSheet.Range("A2")
End Sub
- Remove "Set" when assigning values to variables. It's not required.

Thanks,
Saurabh
 

AyKay11

New Member
Joined
Jul 27, 2012
Messages
6

ADVERTISEMENT

Hi Saurabh,

I've tried this quickly and it doesn't seem to have had any effect. Removing "Set" has stopped the "Object Required" error, but I'm still getting the 1004 error, even after changing the date format.

Thanks
 

Saurabhj

Active Member
Joined
Jun 6, 2020
Messages
413
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
VLOOKUP returns #N/A error, if no match found in the given range. Hence it generates 1004 error.

To handle this you can use On Error Resume next.

VBA Code:
Sub vlookupCheck()
Dim ActivityDate As Date
ActivityDate = Format(Now(), "DD-MM-YYYY")

On Error Resume Next
vResult = Application.VLookup(ActivityDate, ActiveSheet.Range("A:B"), 2, 0)
MsgBox vResult

End Sub
 

AyKay11

New Member
Joined
Jul 27, 2012
Messages
6
Hi Saurabhj,

Thank you for your your time looking into this. I have managed to sort this out now by changing my dates to text, and the lookups now work.

Thanks again
 

Watch MrExcel Video

Forum statistics

Threads
1,127,583
Messages
5,625,622
Members
416,124
Latest member
DeMoNloK

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top