Return values from a Range

T

timh61

New Member
Joined
Sep 3, 2023
Messages
28
Office Version
  1. 365
Platform
  1. Windows
I'm trying to create a vba that would look for this:


If the value of any cells in column B was equal to a value found in column R1 then the adjacent cell in column c would equal the first value in a Range in column D and each instance afterward it would equal the next value in column D, etc.

then

If the value of any cells in column B was equal to a value found in column R2 then the adjacent cell in column c would equal the first value in a Range in column E and each instance afterward it would equal the next value in column E, etc.

then

If the value of any cells in column B was equal to a value found in column R3 the adjacent cell in column c would equal the first value in a Range in column F and each instance afterward it would equal the next value in column F, etc.


I hope this makes sense.

Any help would be appreciated
 
Sort by date Sort by votes
timh61 said:
Thank You!! This works great! I do have another question though. Is there any way to reference the entire range in column R instead of referencing Weld to R1, Fire to R2, etc. In other words, can it just look anywhere in column R to find those phrases and if one is found then fill column C in the same way as you have it in your example? The only reason I ask that, is because there my be more of these codes listed in column R in the future and if someone sorts the column alphabetically then it would change the above coding.
Click to expand...
That is possible, yes. However, I set it up that way because your conditions specifically linked R1 to column D, R2 to column E and R3 to column F. How should it know which column to pull the sequence number from? Will there be enough columns (D to whatever) for the amount of codes in column R?
 
Upvote 0

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
dreid1011 said:
That is possible, yes. However, I set it up that way because your conditions specifically linked R1 to column D, R2 to column E and R3 to column F. How should it know which column to pull the sequence number from? Will there be enough columns (D to whatever) for the amount of codes in column R?
Click to expand...

Yes, there will be enough room in the remaining columns. It would know if it matched "Weld", to pull from D, if it matched "Fire", to pull from E, If it matched "Cos" it would pull from column F, etc.
 
Upvote 0
timh61 said:
Yes, there will be enough room in the remaining columns. It would know if it matched "Weld", to pull from D, if it matched "Fire", to pull from E, If it matched "Cos" it would pull from column F, etc.
Click to expand...
Okay, so the values would be tied to each column D, E, F etc.. Would it only go up to column Q or could it go past R?
 
Upvote 0
dreid1011 said:
Okay, so the values would be tied to each column D, E, F etc.. Would it only go up to column Q or could it go past R?
Click to expand...
It should only go to Q but to be safe, let's move the data in column R down to Column Z
 
Upvote 0
Okay, this has enough Case sections to get you to column Y. You just need to uncomment each additional section as you need them and change the "xxx" to the appropriate value from column Z.
VBA Code: 
Private Sub Cvalue()
Dim i As Long
Dim count() As Long

'Setting enough counters for as many values in column Z
ReDim count(0 To Range("Z" & Rows.count).End(xlUp).Row - 1) As Long

'Initializing counter values to 1
For i = LBound(count, 1) To UBound(count, 1)
    count(i) = 1
Next i

'Select Case based on column B value
'Uncomment additional Case sections below for each unique value in column Z
'Rename "xxx" in each Case section to a value from column Z
For i = 1 To Range("B" & Rows.count).End(xlUp).Row
    Select Case Range("B" & i).Value
        Case "Weld"     'count(0) - Col D
            Range("C" & i).Value = Range("D" & count(0)).Value
            count(0) = count(0) + 1
        Case "Fire"     'count(1) - Col E
            Range("C" & i).Value = Range("E" & count(1)).Value
            count(1) = count(1) + 1
        Case "Cos"      'count(2) - Col F
            Range("C" & i).Value = Range("F" & count(2)).Value
            count(2) = count(2) + 1
'        Case "xxx"      'count(3) - Col G
'            Range("C" & i).Value = Range("G" & count(3)).Value
'            count(3) = count(3) + 1
'        Case "xxx"      'count(4) - Col H
'            Range("C" & i).Value = Range("H" & count(4)).Value
'            count(4) = count(4) + 1
'        Case "xxx"      'count(5) - Col I
'            Range("C" & i).Value = Range("I" & count(5)).Value
'            count(5) = count(5) + 1
'        Case "xxx"      'count(6) - Col J
'            Range("C" & i).Value = Range("J" & count(6)).Value
'            count(6) = count(6) + 1
'        Case "xxx"      'count(7) - Col K
'            Range("C" & i).Value = Range("K" & count(7)).Value
'            count(7) = count(7) + 1
'        Case "xxx"      'count(8) - Col L
'            Range("C" & i).Value = Range("L" & count(8)).Value
'            count(8) = count(8) + 1
'        Case "xxx"      'count(9) - Col M
'            Range("C" & i).Value = Range("M" & count(9)).Value
'            count(9) = count(9) + 1
'        Case "xxx"      'count(10) - Col N
'            Range("C" & i).Value = Range("N" & count(10)).Value
'            count(10) = count(10) + 1
'        Case "xxx"      'count(11) - Col O
'            Range("C" & i).Value = Range("O" & count(11)).Value
'            count(11) = count(11) + 1
'        Case "xxx"      'count(12) - Col P
'            Range("C" & i).Value = Range("P" & count(12)).Value
'            count(12) = count(12) + 1
'        Case "xxx"      'count(13) - Col Q
'            Range("C" & i).Value = Range("Q" & count(13)).Value
'            count(13) = count(13) + 1
'        Case "xxx"      'count(14) - Col R
'            Range("C" & i).Value = Range("R" & count(14)).Value
'            count(14) = count(14) + 1
'        Case "xxx"      'count(15) - Col S
'            Range("C" & i).Value = Range("S" & count(15)).Value
'            count(15) = count(15) + 1
'        Case "xxx"      'count(16) - Col T
'            Range("C" & i).Value = Range("T" & count(16)).Value
'            count(16) = count(16) + 1
'        Case "xxx"      'count(17) - Col U
'            Range("C" & i).Value = Range("U" & count(17)).Value
'            count(17) = count(17) + 1
'        Case "xxx"      'count(18) - Col V
'            Range("C" & i).Value = Range("V" & count(18)).Value
'            count(18) = count(18) + 1
'        Case "xxx"      'count(19) - Col W
'            Range("C" & i).Value = Range("W" & count(19)).Value
'            count(19) = count(19) + 1
'        Case "xxx"      'count(20) - Col X
'            Range("C" & i).Value = Range("X" & count(20)).Value
'            count(20) = count(20) + 1
'        Case "xxx"      'count(21) - Col Y
'            Range("C" & i).Value = Range("Y" & count(21)).Value
'            count(21) = count(21) + 1
        Case Else
            Exit Sub
    End Select
Next i
End Sub
 
Upvote 0
Solution
dreid1011 said:
Okay, this has enough Case sections to get you to column Y. You just need to uncomment each additional section as you need them and change the "xxx" to the appropriate value from column Z.
VBA Code: 
Private Sub Cvalue()
Dim i As Long
Dim count() As Long

'Setting enough counters for as many values in column Z
ReDim count(0 To Range("Z" & Rows.count).End(xlUp).Row - 1) As Long

'Initializing counter values to 1
For i = LBound(count, 1) To UBound(count, 1)
    count(i) = 1
Next i

'Select Case based on column B value
'Uncomment additional Case sections below for each unique value in column Z
'Rename "xxx" in each Case section to a value from column Z
For i = 1 To Range("B" & Rows.count).End(xlUp).Row
    Select Case Range("B" & i).Value
        Case "Weld"     'count(0) - Col D
            Range("C" & i).Value = Range("D" & count(0)).Value
            count(0) = count(0) + 1
        Case "Fire"     'count(1) - Col E
            Range("C" & i).Value = Range("E" & count(1)).Value
            count(1) = count(1) + 1
        Case "Cos"      'count(2) - Col F
            Range("C" & i).Value = Range("F" & count(2)).Value
            count(2) = count(2) + 1
'        Case "xxx"      'count(3) - Col G
'            Range("C" & i).Value = Range("G" & count(3)).Value
'            count(3) = count(3) + 1
'        Case "xxx"      'count(4) - Col H
'            Range("C" & i).Value = Range("H" & count(4)).Value
'            count(4) = count(4) + 1
'        Case "xxx"      'count(5) - Col I
'            Range("C" & i).Value = Range("I" & count(5)).Value
'            count(5) = count(5) + 1
'        Case "xxx"      'count(6) - Col J
'            Range("C" & i).Value = Range("J" & count(6)).Value
'            count(6) = count(6) + 1
'        Case "xxx"      'count(7) - Col K
'            Range("C" & i).Value = Range("K" & count(7)).Value
'            count(7) = count(7) + 1
'        Case "xxx"      'count(8) - Col L
'            Range("C" & i).Value = Range("L" & count(8)).Value
'            count(8) = count(8) + 1
'        Case "xxx"      'count(9) - Col M
'            Range("C" & i).Value = Range("M" & count(9)).Value
'            count(9) = count(9) + 1
'        Case "xxx"      'count(10) - Col N
'            Range("C" & i).Value = Range("N" & count(10)).Value
'            count(10) = count(10) + 1
'        Case "xxx"      'count(11) - Col O
'            Range("C" & i).Value = Range("O" & count(11)).Value
'            count(11) = count(11) + 1
'        Case "xxx"      'count(12) - Col P
'            Range("C" & i).Value = Range("P" & count(12)).Value
'            count(12) = count(12) + 1
'        Case "xxx"      'count(13) - Col Q
'            Range("C" & i).Value = Range("Q" & count(13)).Value
'            count(13) = count(13) + 1
'        Case "xxx"      'count(14) - Col R
'            Range("C" & i).Value = Range("R" & count(14)).Value
'            count(14) = count(14) + 1
'        Case "xxx"      'count(15) - Col S
'            Range("C" & i).Value = Range("S" & count(15)).Value
'            count(15) = count(15) + 1
'        Case "xxx"      'count(16) - Col T
'            Range("C" & i).Value = Range("T" & count(16)).Value
'            count(16) = count(16) + 1
'        Case "xxx"      'count(17) - Col U
'            Range("C" & i).Value = Range("U" & count(17)).Value
'            count(17) = count(17) + 1
'        Case "xxx"      'count(18) - Col V
'            Range("C" & i).Value = Range("V" & count(18)).Value
'            count(18) = count(18) + 1
'        Case "xxx"      'count(19) - Col W
'            Range("C" & i).Value = Range("W" & count(19)).Value
'            count(19) = count(19) + 1
'        Case "xxx"      'count(20) - Col X
'            Range("C" & i).Value = Range("X" & count(20)).Value
'            count(20) = count(20) + 1
'        Case "xxx"      'count(21) - Col Y
'            Range("C" & i).Value = Range("Y" & count(21)).Value
'            count(21) = count(21) + 1
        Case Else
            Exit Sub
    End Select
Next i
End Sub
Click to expand...
This works perfectly!!! Thank you very much!
 
Upvote 0
dreid1011 said:
Okay, this has enough Case sections to get you to column Y. You just need to uncomment each additional section as you need them and change the "xxx" to the appropriate value from column Z.
VBA Code: 
Private Sub Cvalue()
Dim i As Long
Dim count() As Long

'Setting enough counters for as many values in column Z
ReDim count(0 To Range("Z" & Rows.count).End(xlUp).Row - 1) As Long

'Initializing counter values to 1
For i = LBound(count, 1) To UBound(count, 1)
    count(i) = 1
Next i

'Select Case based on column B value
'Uncomment additional Case sections below for each unique value in column Z
'Rename "xxx" in each Case section to a value from column Z
For i = 1 To Range("B" & Rows.count).End(xlUp).Row
    Select Case Range("B" & i).Value
        Case "Weld"     'count(0) - Col D
            Range("C" & i).Value = Range("D" & count(0)).Value
            count(0) = count(0) + 1
        Case "Fire"     'count(1) - Col E
            Range("C" & i).Value = Range("E" & count(1)).Value
            count(1) = count(1) + 1
        Case "Cos"      'count(2) - Col F
            Range("C" & i).Value = Range("F" & count(2)).Value
            count(2) = count(2) + 1
'        Case "xxx"      'count(3) - Col G
'            Range("C" & i).Value = Range("G" & count(3)).Value
'            count(3) = count(3) + 1
'        Case "xxx"      'count(4) - Col H
'            Range("C" & i).Value = Range("H" & count(4)).Value
'            count(4) = count(4) + 1
'        Case "xxx"      'count(5) - Col I
'            Range("C" & i).Value = Range("I" & count(5)).Value
'            count(5) = count(5) + 1
'        Case "xxx"      'count(6) - Col J
'            Range("C" & i).Value = Range("J" & count(6)).Value
'            count(6) = count(6) + 1
'        Case "xxx"      'count(7) - Col K
'            Range("C" & i).Value = Range("K" & count(7)).Value
'            count(7) = count(7) + 1
'        Case "xxx"      'count(8) - Col L
'            Range("C" & i).Value = Range("L" & count(8)).Value
'            count(8) = count(8) + 1
'        Case "xxx"      'count(9) - Col M
'            Range("C" & i).Value = Range("M" & count(9)).Value
'            count(9) = count(9) + 1
'        Case "xxx"      'count(10) - Col N
'            Range("C" & i).Value = Range("N" & count(10)).Value
'            count(10) = count(10) + 1
'        Case "xxx"      'count(11) - Col O
'            Range("C" & i).Value = Range("O" & count(11)).Value
'            count(11) = count(11) + 1
'        Case "xxx"      'count(12) - Col P
'            Range("C" & i).Value = Range("P" & count(12)).Value
'            count(12) = count(12) + 1
'        Case "xxx"      'count(13) - Col Q
'            Range("C" & i).Value = Range("Q" & count(13)).Value
'            count(13) = count(13) + 1
'        Case "xxx"      'count(14) - Col R
'            Range("C" & i).Value = Range("R" & count(14)).Value
'            count(14) = count(14) + 1
'        Case "xxx"      'count(15) - Col S
'            Range("C" & i).Value = Range("S" & count(15)).Value
'            count(15) = count(15) + 1
'        Case "xxx"      'count(16) - Col T
'            Range("C" & i).Value = Range("T" & count(16)).Value
'            count(16) = count(16) + 1
'        Case "xxx"      'count(17) - Col U
'            Range("C" & i).Value = Range("U" & count(17)).Value
'            count(17) = count(17) + 1
'        Case "xxx"      'count(18) - Col V
'            Range("C" & i).Value = Range("V" & count(18)).Value
'            count(18) = count(18) + 1
'        Case "xxx"      'count(19) - Col W
'            Range("C" & i).Value = Range("W" & count(19)).Value
'            count(19) = count(19) + 1
'        Case "xxx"      'count(20) - Col X
'            Range("C" & i).Value = Range("X" & count(20)).Value
'            count(20) = count(20) + 1
'        Case "xxx"      'count(21) - Col Y
'            Range("C" & i).Value = Range("Y" & count(21)).Value
'            count(21) = count(21) + 1
        Case Else
            Exit Sub
    End Select
Next i
End Sub
Click to expand...
This works perfectly!!! Thank you very much
dreid1011 said:
You're welcome. I could probably work it so there aren't so many case selections if you desire some shorter code.
Click to expand...
That would be cool if you could do that!!
 
Upvote 0
timh61 said:
The only reason I ask that, is because there my be more of these codes listed in column R in the future and if someone sorts the column alphabetically then it would change the above coding.
Click to expand...
With the above in mind, does the sort order of column Z change which column (D:Y) matches each code? Or will column D always be Z1, col E - Z2, col F - Z3 etc?
 
Upvote 0
dreid1011 said:
With the above in mind, does the sort order of column Z change which column (D:Y) matches each code? Or will column D always be Z1, col E - Z2, col F - Z3 etc?
Click to expand...
No. That will always remain constant. As Long as any cell in column B matched any cell in column Z,(no matter where it is positioned) with the word "Weld" then it would look in column D, "Fire" would always make it look in column E, "Cos" would always look in column F, etc.
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
Loop to Cut and Paste Between Two String Occurrences
Replies
3
Views
190
Trebor76
T
D
  • Question
Return largest value from other variable
Replies
4
Views
167
felixstraube
F
A
  • Solved
Xlookup with Multiple Criteria
Replies
5
Views
775
acool
A
E
  • Question
Specific VBA Function
Replies
0
Views
189
EAlexWolfe
E
C
  • Solved
Sum of Lowest Rankings
2
Replies
10
Views
257
Fluff
Fluff

Forum statistics

Threads
1,221,310
Messages
6,159,176
Members
451,543
Latest member
cesymcox

Share this page

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
Back
Top