Please help me search a range; output matches to another sheet

[eggo]

I'm trying to write a macro (Excel 2007) that will iterate through one column at a time, searching for a value of "n", and output the value from column A to another sheet. I have managed to make it work for one column, but trying to get it to increment through the other columns is proving more difficult than expected.


Here is the code I used to pull the information from one column

Set i = Sheets("ACHR (pm)")
Set e = Sheets("ACHR (PM) Schedule")
Dim outRow
Dim inRow
outRow = 1
inRow = 7

Do Until IsEmpty(i.Range("B" & j))

If i.Range("B" & j) = "n" Then
d = d + 1
e.Rows(d).Value = i.Rows(j).Value

End If
j = j + 1
Loop

​

It works fine, but pulls the whole row (I only need the value from column A) and only searches column B.
I tried to wrap the loop in another loop to increment the column after reaching the bottom of the column;

Dim i
Dim e
Set i = Sheets("HVAC (PM)")
Set e = Sheets("HVAC (PM) Schedule")

Dim outRow
Dim inRow
Dim outCol As String
Dim inCol As String

outRow = 1
inRow = 7
outCol = "A"
inCol = "B"

Do Until IsEmpty(i.Range(inCol & inRow))
inRow = 7
Do Until IsEmpty(i.Range(inCol & inRow))

If i.Range(inCol & inRow) = "n" Then
outRow = outRow + 1
e.Cells(outCol, outRow).Value = i.Cells(inCol, inRow).Value
inRow = inRow + 1
End If

Loop
inCol = inCol + 1
outCol = outCol + 1
Loop
End Sub
​

This gets stuck in an endless loop. I can't seem to figure out what is going wrong. Any help would be very much appreciated.
It is entirely likely I've fubar'd the syntax or even gone about this the wrong way, if you know of a better solution or if you could help me make my solution work, I would be eternally in your debt.

 

[hiker95]

eggo,

Can we see some of your raw data, and the results you are looking for?

Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Excel Jeanie HTML 4 (contains graphic instructions).
http://www.excel-jeanie-html.de/html/hlp_schnell_en.php

Or, you can upload your workbook to www.box.net and provide us with a link to your workbook.

 

[JoeMo]

Here's some code that may accomplish what I think you are asking for. It finds every cell containing "n" or "N" (easy to change this to find only lower case "n), then copies the value in Column A from the row with an "n" in it and pastes it into Column A of a new sheet added immediately after the sheet being searched.

Sub test10()
Dim sRng As Range, rRng As Range, sSht As Worksheet, rSht As Worksheet
Dim r As Long, firstAddress As String, ctr As Long
Set sSht = ActiveSheet
Set sRng = sSht.UsedRange.Find("n", Cells(1, 1), xlValues, xlWhole, xlByRows, xlNext _
, False)
If sRng Is Nothing Then
    MsgBox "search value not found"
    Exit Sub
End If
ctr = 1
firstAddress = sRng.Address
Set rSht = Sheets.Add(after:=sSht)
r = sRng.Row
sSht.Cells(r, 1).Copy rSht.Cells(ctr, 1)

Do
    With sSht.UsedRange
        r = sRng.Row
        .Cells(r, 1).Copy rSht.Cells(ctr, 1)
        Set sRng = .FindNext(sRng)
        ctr = ctr + 1
    End With
Loop While Not sRng Is Nothing And sRng.Address <> firstAddress
    
End Sub

 

[eggo]

JoeMo: that script works great, finds all the right rows, the only problem is with the output. How would I change it to output the names in the column (on the new sheet) that corresponds to the column where the "n" was found?

hiker95: I am unable to access box.com (web content filter) and I don't have rights to install jeanie-html.

 

[eggo]

Never mind, I've got it figured out, I used JoeMo's code with a few changes:

Sub findN()
Dim sRng As Range, rRng As Range, sSht As Worksheet, rSht As Worksheet
Dim r As Long, c As Long, firstAddress As String, ctr As Long
Set sSht = ActiveSheet
Set sRng = sSht.UsedRange.Find("n", Cells(1, 1), xlValues, xlWhole, xlByRows, xlNext _
, False)
If sRng Is Nothing Then
    MsgBox "search value not found"
    Exit Sub
End If
ctr = 2
firstAddress = sRng.Address
Set rSht = Sheets.Add(after:=sSht)
r = sRng.Row
c = sRng.Column
sSht.Cells(r, 1).Copy rSht.Cells(ctr, 1)

Do
    With sSht.UsedRange
        r = sRng.Row
        c = sRng.Column
        .Cells(r, 1).Copy rSht.Cells(r, c)
        Set sRng = .FindNext(sRng)
        ctr = ctr + 1
    End With
Loop While Not sRng Is Nothing And sRng.Address <> firstAddress
    
Dim colNum As Long
Dim x As Range

For colNum = 2 To 12
    With ActiveSheet.Columns(colNum)
        For Each x In Range(.Cells(colNum, 2), Range("IV1").End(xlToLeft))
        x.EntireColumn.sort key1:=x, order1:=xlAscending, Header:=xlYes
        Next x
    End With
Next colNum


End Sub

Thanks for the help guys! (or gals)