Return values from a Range

[timh61]

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

 

[dreid1011]

Could you provide some sample data and expected results please? I am not sure I follow the last part of each query, "and each instance afterward it would equal the next value in column ..."

 

[timh61]

Could you provide some sample data and expected results please? I am not sure I follow the last part of each query, "and each instance afterward it would equal the next value in column ..."

Here is a table with the expected results in column C

So the first Weld in column B was assigned the value in D1, the second Weld was assigned the value in D2, the third Weld was assigned the value in D3
The first COS in column B was assigned the value in F1, the first Fire in column B was assigned the value in E1 and the second Fire was assigned the value in E2


A B C D E F R

Name1

Weld

1​

1​

7​

13​

Weld

Name2

Cos

13​

2​

8​

14​

Fire

Name3

Fire

7​

3​

9​

15​

Cos

Name4

Weld

2​

4​

10​

16​

Name5

Weld

3​

5​

11​

17​

Name6

Fire

8​

6​

12​

18​

 

[timh61]

Here is a table with the expected results in column C

So the first Weld in column B was assigned the value in D1, the second Weld was assigned the value in D2, the third Weld was assigned the value in D3
The first COS in column B was assigned the value in F1, the first Fire in column B was assigned the value in E1 and the second Fire was assigned the value in E2


A B C D E F R

Name1

Weld

1​

1​

7​

13​

Weld

Name2

Cos

13​

2​

8​

14​

Fire

Name3

Fire

7​

3​

9​

15​

Cos

Name4

Weld

2​

4​

10​

16​

Name5

Weld

3​

5​

11​

17​

Name6

Fire

8​

6​

12​

18​

Sorry, here is a better view of the data

 

[dreid1011]

What happens if there are more matches than numbers in D:F? For instance, the sample contains 6 numbers in col D. What happens if there are 7 or more instances of Weld? Is that possible?

 

[timh61]

What happens if there are more matches than numbers in D:F? For instance, the sample contains 6 numbers in col D. What happens if there are 7 or more instances of Weld? Is that possible?

No. That wouldn't be possible. I will always have the numbers in columns D, E, F reflect the maximum number of times that they could appear in column B

 

[dreid1011]

No. That wouldn't be possible. I will always have the numbers in columns D, E, F reflect the maximum number of times that they could appear in column B

Okay, give this a try then:

Private Sub Cvalue()
Dim i, j, k, l As Long

j = 1   'Column D counter
k = 1   'Column E counter
l = 1   'Column F counter

For i = 1 To Range("B" & Rows.Count).End(xlUp).Row
    Select Case Range("B" & i).Value
        Case Range("R1").Value  'Weld
            Range("C" & i).Value = Range("D" & j).Value
            j = j + 1
        Case Range("R2").Value  'Fire
            Range("C" & i).Value = Range("E" & k).Value
            k = k + 1
        Case Range("R3").Value  'Cos
            Range("C" & i).Value = Range("F" & l).Value
            l = l + 1
        Case Else
            Exit Sub
    End Select
Next i
End Sub

 

[timh61]

Okay, give this a try then:

Private Sub Cvalue()
Dim i, j, k, l As Long

j = 1   'Column D counter
k = 1   'Column E counter
l = 1   'Column F counter

For i = 1 To Range("B" & Rows.Count).End(xlUp).Row
    Select Case Range("B" & i).Value
        Case Range("R1").Value  'Weld
            Range("C" & i).Value = Range("D" & j).Value
            j = j + 1
        Case Range("R2").Value  'Fire
            Range("C" & i).Value = Range("E" & k).Value
            k = k + 1
        Case Range("R3").Value  'Cos
            Range("C" & i).Value = Range("F" & l).Value
            l = l + 1
        Case Else
            Exit Sub
    End Select
Next i
End Sub

Thank you! Should this be placed in view code section of the sheet?

 

[dreid1011]

In the sheet module, yes. In this case, I was using Sheet3.

 

[timh61]

In the sheet module, yes. In this case, I was using Sheet3.
View attachment 116037

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.