VBA help for looping

**EXCELcior08** · 10-16-2010, 09:15 PM

Hi,
I am a total noob at VBA so you'll have to get your had ready for a few facepalms. In any case. I'm trying to get Excel 2007 to search a range for the number "1", if it finds number one I want it to look at the top of that column and copy the value there to paste in a variable cell. so it may look something like this:

CAR|BAG|CUP <----These are the words that I want copied
N/A | N/A | N/A
N/A | N/A | N/A
N/A | N/A | N/A
1 | 1 | 1 <----these three cells are the range that I want to search for one

I know that I need to use the find function for the searching, my question is how do I tell it to look at the top of the column, also the N/A's could be replaced by ones so I can't use the function that simply looks for the next value up.

my other question is how would the "for" loop need to be structured in order for it to copy the corresponding word for each "1"?

Thanks in advance for any help. It is very much appreciated.

**Leith Ross** · 10-16-2010, 11:10 PM

Hello EXCELcior08,

Welcome to the Forum!

Reminds of Longfellow's poem..

The shades of night were falling fast,
As through an Alpine village passed
A youth, who bore, 'mid snow and ice,
A banner with the strange device,
Excelsior!

The Find method for a Range object returns a Range object. You can then manipulate the Range and the Worksheet to which it belongs to either read or write data to a specific cell or cells.

Here are a couple questions I have for you. Once you answer these, I can give you some examples of For loops using Find.[list="*"][*] Is the search looking for all three cells to contain a "1" or any of the cells to contain a "1"? [*] Are you searching for just one row or all rows that contain "1"?

**EXCELcior08** · 10-17-2010, 11:21 AM

hi,
Thanks for you reply!

To answer your questions, the search is looking for just one "1", but should be able to find and do the copying of the first cell at the top of their specific colum for more than just one "1".
The second question, made me realize I may have missed something, in the description of the problem. The "1s" will be on the same row as the result of a previous search. So:

1|1|1|1|1|KEYWORD| <--- this row.

I guess the short answer is, it will be only one row.

Once again thanks for taking your time to help a noob out.

**Leith Ross** · 10-17-2010, 11:44 AM

Hello EXCELcior08,

I am still a bit unclear about what you are doing. If you post your workbook, it would clear things up.

To Attach a File:

Scroll down to the window below your post Additional Options
In the frame Attach Files you will see the button Manage Attachments
Click the button.
A new window will open titled Manage Attachments - Excel Forum.
Click the Browse... button to locate your file for uploading.
This will open a new window File Upload.
Once you have located the file to upload click the Open button. This window will close.
You are now back in the Manage Attachments - Excel Forum window.
Click the Upload button and wait until the file has uploaded.
Close the window and then click Submit.

**EXCELcior08** · 10-17-2010, 12:03 PM

Thanks for your quick reply,

I'm having some difficulty uploading the file, my internet connection may be to blame, but here is what I imagined the for loop in doing:

for each "1" in "variable range (lets say G4-J4)", check top cell in that column, and copy top cell's value to a variable cell (lets say A2,B2,C2,D2).

The result:

cell value of G1 is in A2
cell value of H1 is in B2
cell value of I1 is in C2
cell value of J1 is in D2

Once again thanks for your patience.

**Leith Ross** · 10-17-2010, 02:53 PM

Hello EXCELcior08,

Here is one way to do it.

Sub Example_1()

  Dim I As Long
  Dim Rng1 As Range
  Dim Rng2 As Range
  
    Set Rng1 = Range("G4:J4")
    Set Rng2 = Range("A2:D2")
    
      For I = 1 To Rng1.Cells.Count
        If Rng1.Cells(1, I) = "1" Then
           Rng2.Cells(1, I) = Cells(1, Rng1.Cells(1, I).Column)
        End If
      Next I
      
End Sub

**EXCELcior08** · 10-17-2010, 03:22 PM

Thank you so much, the code it perfectly what I need. I hope I didn't cause too many moments of "facepalm".

Thanks.

PS how do I set this thread to solved?