Is it possible to look for certain data in a column

[wafs]

Here is a code that I requested to look for an input and compare to a column. It looks like it is working except the output is coping over all TestRange3 and not just ones that match the header. I have several different numbers in TestRange 2 and I need this to be able to go through the column and just pull out what I have requested. All help would be greatly appreciated.

Dim cellstart As Integer 'starting point of informaiton (row)
Dim p As Integer
Dim n As String 'counting variable
Dim TestRange As String 'Test String
Dim TestRange2 As String 'Test String
Dim TestRange3 As String 'Test String
Dim Output(1 To 10) As String
Dim m As String
Dim sh As String
Dim destsh As String
Dim reportlocation As String
Dim header As String 'header you want to run Holds header

cellstart = 3 'Starting point of source sheet
p = 0
header = InputBox("Which header do you want to run?", "header") 'input the requested header

sh = "Daily" 'Work sheet where the header information is kept
destsh = header ' desination sheet for header

n = cellstart
TestRange2 = "B" + n 'column where to match header request
TestRange3 = "A" + n

Do While header <> Sheets(sh).Range(TestRange2)
p = p + 1
m = p + 4 'Tells where to start importing information on dest sheet
reportlocation = "A" + m 'starting point of output file
Sheets(sh).Range(TestRange3).Copy Sheets(destsh).Range(reportlocation)
TestRange2 = "B" + n
TestRange3 = "A" + n
n = n + 1
Loop
End Sub

 

[SkipVought]

It sure would be helpful to post some sample data for which this code is demonstrating as described.

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[SkipVought]

your copy occurs in the loop that says the header <> [testrange2]???

change your logic!


Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[wafs]

I changed my logic and now it goes from the do while to the end sub. It doesn't read the rest of the macro. I just need the macro to go down a column and copy of information if it matches the header that I keyed in.

 

[SkipVought]

Then you don't want to stop processing then column if there is no match. You only want to copy if there is a match.

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[wafs]

Here is an updated code, and it won't run if the first header number is different then what I'm asking for. it goes right from the if statement to the end sub. If the first number is what i'm asking for it runs that, but stops at the next row if it is different then what I'm asking for. How can I get the code to keep running even if the next row does not match. do I need to move my copy statement after the else??

Dim cellstart As Integer 'starting point of informaiton (row)
Dim p As Integer
Dim x As Integer
Dim n As String 'counting variable
Dim TestRange As String 'Test String
Dim TestRange2 As String 'Test String
Dim TestRange3 As String 'Test String
Dim Output(1 To 10) As String
Dim m As String
Dim sh As String
Dim destsh As String
Dim reportlocation As String
Dim header As String 'header you want to run Holds header

cellstart = 3 'Starting point of source sheet
p = 0
header = InputBox("Which header do you want to run?", "header") 'input the requested header
TestRange = header
sh = "Daily" 'Work sheet where the header information is kept
destsh = header ' desination sheet for header

n = cellstart
TestRange2 = "B" + n 'column where to match header request
TestRange3 = "A" + n
If TestRange = Sheets(sh).Range(TestRange2) Then
p = p + 1
m = p + 3 'Tells where to start importing information on dest sheet
reportlocation = "A" + m 'starting point of output file
Sheets(sh).Range(TestRange3).Copy Sheets(destsh).Range(reportlocation)
n = n + 1
TestRange2 = "B" + n
TestRange3 = "A" + n
Else
End If

End Sub

 

[SkipVought]

Well what values are in testrange and Sheets(sh).Range(TestRange2) at that point?

Put a BREAK in your code and use the Watch Window to observe the key values.

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[PHV]

You have NO loop construct in your code ...

 

[SkipVought]

I'd probably change the code...

 If UCase(Trim(TestRange)) = UCase(Trim(Sheets(sh).Range(TestRange2))) Then

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[wafs]

I will give it a try. Thanks for you help.

 

[wafs]

Thanks guys, it works. I appreciate all your help.