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]

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.

Okay, do you have all the values, or most of them, and their associated columns for me to put in the code or do you want to do that yourself?

 

[dreid1011]

Okay, do you have all the values, or most of them, and their associated columns for me to put in the code or do you want to do that yourself?

Actually, you have options. The values can either be hardcoded in the code itself, or you can enter the values in column Z, then enter their respective column letters in column AA like so:

Book3 8-29-2024.xlsm
	Z	AA
1	Weld	D
2	Fire	E
3	Cos	F
4	Sin	G
5	Tan	H
Sheet1

 

[timh61]

Actually, you have options. The values can either be hardcoded in the code itself, or you can enter the values in column Z, then enter their respective column letters in column AA like so:

Book3 8-29-2024.xlsm
	Z	AA
1	Weld	D
2	Fire	E
3	Cos	F
4	Sin	G
5	Tan	H
Sheet1

Actually....... I think it would be better to enter the values in column z and the letters in column AA instead of hard coding them. That way if they change in the future then the code doesn't have to be re-written

 

[dreid1011]

Actually....... I think it would be better to enter the values in column z and the letters in column AA instead of hard coding them. That way if they change in the future then the code doesn't have to be re-written

Alright, with that said, give this a try:

Private Sub Cvalue()
Dim i, j, cI As Long
Dim count
Dim data

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

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

'Setting data size to match columns Z:AA
ReDim data(1 To Range("Z" & Rows.count).End(xlUp).Row, 1 To Range("AA" & Rows.count).End(xlUp).Row) As String

'Initializing data with values in columns Z:AA
data = Range("Z1:AA" & Range("AA" & Rows.count).End(xlUp).Row).Value

For i = 1 To Range("B" & Rows.count).End(xlUp).Row
    For j = LBound(data, 1) To UBound(data, 1)
        cI = Asc(data(j, 2)) - 67
        If Range("B" & i).Value = data(j, 1) Then
            Range("C" & i).Value = Range(data(j, 2) & count(cI)).Value
            count(cI) = count(cI) + 1
        End If
    Next j
Next i
End Sub

 

[timh61]

Thank you. I've placed this in the worksheet and added the corresponding column numbers for each code in column AA but I can't get it to fire. It gives me an error "subscript out of range"

 

[dreid1011]

Thank you. I've placed this in the worksheet and added the corresponding column numbers for each code in column AA but I can't get it to fire. It gives me an error "subscript out of range"

Column numbers or letters?

 

[timh61]

Column numbers or letters?

letters

 

[dreid1011]

letters

Okay. What row does your data start in?

 

[timh61]

letters

I just realized that I didn't have all of the letters in caps. Once I changed those to caps, everything works great!!!

 

[dreid1011]

I just realized that I didn't have all of the letters in caps. Once I changed those to caps, everything works great!!!

Perfect! Glad to hear.