Loop within a loop

[Yusuf]

Hi everyone

I need to find the last cell to the right and enter a value in the cell after this.

But first, I have to find the correct match down. (This I can do, but not including the bit above, xlup...)

Example.
A1 = cat & A2 = Brown

A3 = dog
A4 = rabbit
A5 = cat & B5 = Grey

If Column B, C, D etc... have values alongside the names in ColA, then the code must find the first empty cell and fill it with A2.Value
The result in this example will mean that Cell C5 = 'Brown'


Any help with this will be much appreciated

Yusuf

 

[VoG]

Try

Sub test()
Dim LR As Long, LC As Integer
LR = Cells(Rows.Count, "A").End(xlUp).Row
LC = Cells(LR, Columns.Count).End(xlToLeft).Column
Cells(LR, LC + 1).Value = Cells(2, 1).Value
End Sub

 

[Yusuf]

Thanks VoG

By the example I sent, this works perfectly.

Unfortunately, I need the code to find the matching name in the list rather than the last entry.

In fact, is it possible reference down and across at the same time?

For example,
Using the example of pet names I used before with A6.value='Rabbit'
B2 = January
C2 = February
D2 = March
E2 = April

If in Range(A3:A6), (A1.value = Cat) is in A5
and Range(B2:E2), find 'March'

The answer (A2.Value = Brown) will be in Cell:D5

Is this possible?

Many thanks
Yusuf

 

[Norie]

Yusuf

This doesn't make a lot of sense I'm afraid, to me anyway, and the title of your thread confuses things further.

What do you mean by 'loop within a loop'?

 

[Yusuf]

Hi Norie

I am a novice with VBA and I've been pulling my hair out trying to sort this on my own.
The only way I thought this could be done is have a loop referencing the range down while the second loop references the range across.

Looking at my example, I should rather use 2 Textboxes. Textbox1.value = 'Cat' and Textbox2.value = 'Brown'.

This will make the range down = Pet names and the range across = Months
A2= dog
A3 = rabbit
A4 = cat
A5 = mouse
and
B1 = January
C1 = February
D1 = March
E1 = April

If range(A2:A5) = Textbox1.value and Range(B2:E2) = 'March' then
Cell(D4) = Textbox2.value (i.e. 'Brown')

I hope this makes sense.

Thanks for helping
Yusfu

 

[Yusuf]

Hi

Does anyone know if this is possible?

Regards
Yusuf

 

[Yusuf]

Anybody?