Loop within a loop

Yusuf

Active Member
Joined
Jun 1, 2004
Messages
337
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
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

Code:
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

Active Member
Joined
Jun 1, 2004
Messages
337
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,232
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Jun 1, 2004
Messages
337

ADVERTISEMENT

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

Active Member
Joined
Jun 1, 2004
Messages
337
Hi

Does anyone know if this is possible?

Regards
Yusuf
 

Watch MrExcel Video

Forum statistics

Threads
1,123,385
Messages
5,601,328
Members
414,444
Latest member
lionking15

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
Top