Creating a string using Vlookups in VBA

[AyKay11]

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.

 

[Saurabhj]

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]

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]

- Yes, please check the date format. It would resolve your issue. You can use Format to change the date format.

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]

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]

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.

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]

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

 

[Saurabhj]

Great. Thanks for the response.