Assigning and declaring a alphanumeric value to a variable and referrring it through EXCEL VBA

[Vinodhkumar]

Hi All,

I am new to this forum. But I am happy that I have registered with this because I have learnt lot many things through this forum. You people rock!!!!! And thank you!!!!

Now I have one requirment, which is bothering me for the past 3 days. Hope you people can easily get me out of this.

How should we declare and assign a alphanumeric value to variable in Excel VBA and refer that through code? and also could you please tweak the code to finish the process I have mentioned under: Requirement.

Note:
Unique ID sheet : Holds all the Unique Ids of the values.
Sheet1: Holds 'alphanumeric' and also 'String values' in two separate columns
Sheet2: Holds 'Aplhanumeric' values in a column


My Code is like this:

sub copyIDs()

For i = 2 to 100

Id = worksheets("UniqueID").cells(i,2).value

if (worksheets("Sheet1").cells(i,2).value like Id) then
if (worksheets("Sheet2").cells(i,2).value like Id) then
worksheets("Sheet2").cells(i,3).value = worksheets("Sheet1").cells(i,2).value
end if
next
end sub

REQUIREMENT
What it should do is, from the 'Unique ID' sheet it should take the first value, check it with 'Sheet1' and 'Sheet2' value, if the ID is present then it should copy the 'Name' from Sheet1 corresponding to the alphanumeric value and should paste in sheet2 adjacent to the alphanumeric value present there.


I need your help in finishing this task. One of the challenges i faced is that 'I am not able to assign the alphanumeric value to the variable 'ID'. It always return me a blank.'

 

[Vinodhkumar]

Sorry if im not clear some where. I need to finish this before tomorrow 8:00 PM (IST). So in a hurry to get this posted here and try some help from you people.

 

[GRTorres]

Can you please supply an example of what would be on each sheet and the outcome: example:

Unique ID Sheet: ABCD1234
Sheet1: ABCD1234 with "Test" as String
Sheet2: ABCD1234

unique ID is on all sheets, copy test and paste next to ABCD1234 on sheet2, something like that?

 

[Vinodhkumar]

Hi Torres, you right.
UniqueIDs will be same like you have mentioned but with just two aplbhabets and 5 digits. (like AB12345)
and Sheet1 will have this Unique ID there and also a text value in the adjacent cell. (like: D1: AB12345 and E1:Text)
and Sheet2 will have the unique ID alone. So have to copy the 'Text' into the adjacent column of AB12345 in Sheet2

 

[GRTorres]

See if this will do the trick, change the columns to fit your sheets.

Sub UniqueIDLookup()

Set a = Sheets("Unique ID")
Set b = Sheets("Sheet1")
Set c = Sheets("Sheet2")
Dim i As Long
Dim findValue As Variant
Dim return1, return2 As Range

For i = 1 To a.Range("B" & Rows.Count).End(xlUp).Row
findValue = a.Range("B" & i)
Set return1 = b.Range("D:D").Find(What:=findValue, LookIn:=xlFormulas, LookAt:=xlWhole)
Set return2 = c.Range("D:D").Find(What:=findValue, LookIn:=xlFormulas, LookAt:=xlWhole)

If return1 Is Nothing Or return2 Is Nothing Then
    i = i
    Else
    return2.Offset(0, 1) = return1.Offset(0, 1)
End If

Next i

End Sub