Variable assignment in adjacent column

[Spacelion]

So I found this great code on another forum (author escapes me right now) and it looks for a word in column A for example, and assigns a chosen variable to the adjacent column. I'm trying to figure out how to adjust this macro so that it finds cells based on a manually selected range rather than a text input. So in example, it'll find numbers from a subset, and I can assign those numbers a variable.


Any help is much appreciated.


Sub Varassign()
Dim IB As String, IB1 As String
IB = InputBox("Enter word to find")
IB1 = InputBox("Enter word to paste next to")
Cells.Find(What:=IB, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Offset(0, 1).Copy Destination:=Cells.Find(What:=IB1, _
After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Offset(0, 1)
End Sub

[stnkynts]

Could you please clarify what you mean by "manually selected range rather than a text input", "subset", and "assign those numbers a variable". My inquiry is primarily in which way you would like to do these things. For example, will you be highlighting the range or do you want to input it. Do you only need to search column A? Etc, etc. Thanks.

[Spacelion]

so far here is what i have, and it prompts properly, but I can't figure out exactly what's wrong.

Sub Varassign()
Dim Specifiedrange As Range, IB1 As String
Set Specifiedrange = Application.InputBox("Specifiy the range to be named: 'Table'", Type:=8)
IB1 = InputBox("Enter word to paste next to")
Cells.Find(What:=IB, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Offset(0, 1).Copy Destination:=Cells.Find(What:=IB1, _
After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Offset(0, 1)
End Sub

[Spacelion]

Sorry, to clarify, yes, a highlighted range. And only from Column A.

What I have is about 1000 cases from A2-A1001 that I would like to assign variables to in column B, based on a highlighted selection from a different sheet in the same workbook. I would do this manually, but there's quite a wide range of variables, hence the prompting for a chosen variable I have it doing right now.

My VBA skills are still quite raw, so anything helps.

[stnkynts]

I am still extremely confused as to what you are trying to do. So say for example we select a range say Sheet1 A1 to A5. Now what? You want to assign variable to column B in that range? What variable? From a inputbox? If this is the case then the Find method is not required.

Just for reference your second post is flawed for many reasons. It is trying to find "IB" in the whole worksheet but you no longer identify what IB is. To be honest i doubt any of your code is needed to accomplish what you want to accomplish.

[Spacelion]

I am still extremely confused as to what you are trying to do. So say for example we select a range say Sheet1 A1 to A5. Now what? You want to assign variable to column B in that range? What variable? From a inputbox? If this is the case then the Find method is not required.

Just for reference your second post is flawed for many reasons. It is trying to find "IB" in the whole worksheet but you no longer identify what IB is. To be honest i doubt any of your code is needed to accomplish what you want to accomplish.

*Attached a sample workbook.

Column A is full of the unique entries, Column C is a randomly selected subset. Column B is where I would like to assign the variables of numbers found in C, that are also in A.

[stnkynts]

Ok, now we are getting somewhere. Let me make sure I am on the right path. You want to:

1. For each value in column C check to see if there is an equivalent value in column A.
2. If there is then you want to enter a variable, which you will generate in an inputbox, in column B.
3. The variable in column B will be the same row reference as that of column A.

Does that sound about right? If so try this:

Please Login or Register  to view this content.