Urgent excel problem....

[h.]

I have a workbook with multiple sheets. In sheet1 there is the output of a
lab machine. It only consists of a column A that is filled with many rows.
Something like this:
Hysteresis 1,1 : 3.00%
Hysteresis 1,2 : 3.00%
Meten display 1,1 : Eff. waarde (V)
Meten display 1,2 : Eff. waarde (V)
Weergeven meting display 1,1 : Ch1
Weergeven meting display 1,2 : Ch2
Impedantie display 1,1 : 600.0000
Impedantie display 1,2 : 600.0000
Impedantie display 2,3 : 600.0000
Laag waarde display 1,1 : -20.0000
Laag waarde display 1,2 : -20.0000
Hoog waarde display 1,1 : 20.0000
Hoog waarde display 1,2 : 20.0000
Datum Tijd Display 1,1 Display 1,2 Display 1,3 Display 2,1
Display 2,2 Display 2,3
30-1-07 10:53:16 0.3 0.3 5.3 0.33 0.34
0.04
30-1-07 10:58:16 0.3 0.3 6.0 0.33
0.34 0.74
30-1-07 11:03:16 0.3 0.3 6.6 0.33
0.34 1.39
30-1-07 11:08:16 0.3 0.3 6.8 0.33
0.34 1.62
30-1-07 11:13:16 0.3 0.3 6.9 0.33
0.34 1.70
30-1-07 11:18:16 0.3 0.3 7.0 0.33
0.34 1.75
30-1-07 11:23:16 0.3 0.3 7.0 0.32
0.34 1.80
30-1-07 11:28:16 0.3 0.3 7.0 0.33
0.34 1.82
30-1-07 11:33:16 0.3 0.3 7.1 0.33
0.34 1.83
30-1-07 11:38:16 0.3 0.3 7.1 0.33
0.34 1.85
30-1-07 11:43:16 0.3 0.3 7.1 0.33
0.34 1.86
30-1-07 11:48:16 0.3 0.3 7.1 0.33
0.34 1.86
30-1-07 11:53:16 0.3 0.3 7.1 0.33
0.34 1.86
30-1-07 11:58:16 0.3 0.3 7.1 0.33
0.34 1.86
30-1-07 12:03:16 0.3 0.3 7.1 0.33
0.34 1.86
30-1-07 12:08:16 0.3 0.3 7.1 0.33
0.34 1.85
30-1-07 12:13:16 0.3 0.3 7.1 0.32
0.34 1.85
30-1-07 12:18:16 0.3 0.3 7.1 0.33
0.34 1.84
30-1-07 12:23:16 0.3 0.3 7.1 0.33
0.34 1.83
30-1-07 12:28:16 0.3 0.3 7.1 0.33
0.34 1.82
30-1-07 12:33:16 0.3 0.3 7.0 0.33
0.34 1.81
30-1-07 12:38:16 0.3 0.3 7.0 0.32
0.34 1.80
30-1-07 12:43:16 0.3 0.3 7.0 0.32
0.34 1.79
30-1-07 12:48:16 0.3 0.3 7.0 0.32
0.34 1.78
30-1-07 12:53:16 0.3 0.3 7.0 0.33
0.34 1.77
30-1-07 12:58:16 0.3 0.3 7.0 0.33
0.34 1.76
30-1-07 13:03:16 0.3 0.3 7.0 0.33
0.34 1.75
30-1-07 13:08:16 0.3 0.3 7.0 0.32
0.34 1.74
30-1-07 13:13:16 0.3 0.3 7.0 0.33
0.34 1.74
30-1-07 13:18:16 0.3 0.3 7.0 0.33
0.34 1.73
30-1-07 13:23:16 0.3 0.3 6.9 0.32
0.34 1.72
30-1-07 13:28:16 0.3 0.3 6.9 0.33
0.34 1.71
30-1-07 13:33:16 0.3 0.3 6.9 0.32
0.34 1.70
30-1-07 13:38:16 0.3 0.3 6.9 0.33
0.34 1.69
30-1-07 13:43:16 0.3 0.3 6.9 0.33
0.34 1.68
30-1-07 13:48:16 0.3 0.3 6.9 0.32
0.34 1.67
30-1-07 13:53:16 0.3 0.3 6.9 0.32
0.34 1.67
30-1-07 13:58:16 0.3 0.3 6.9 0.32
0.34 1.66
30-1-07 14:03:16 0.3 0.3 6.9 0.32
<<<<<<

So, again, every line is the content of a single cel in sheet1 Column A.

In sheet 2 , in cel A1, is only one of the date cells from the sheet1. For
instance:

30-1-07 11:53:16 0.3 0.3 7.1 0.33
0.34 1.86

The content of this cel A1 in sheet 2 may vary! It depends on the output of
a datalogger that puts it in this cel. So it varys. But is is always equal
to ONE OF the lines in Sheet1.

Now I wanted to record a macro that simply uses the find function to find
the content of cel Sheet2!A1 in Sheet1 and GOTO that cel. Next, I need to
select all rows of column A from Sheet 1 underneath the found line.
My recorded macro looked like:

Sub RawColumn()
Sheets("Sheet2").Select
Range("A1").Select
Selection.Copy
Sheets("Sheet1").Select
Range("A1").Select
Cells.Find(What:= _
"30-1-07 11:53:16 0.3 0.3 7.1
0.33 0.34 1.86" _
, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart,
SearchOrder:= _
xlByColumns, SearchDirection:=xlNext, MatchCase:=False).Activate
Range(Selection, Selection.End(xlDown)).Select
........etcetra
End Sub

This works of course, but only as long as the content of sheet2!A1 is the
same.
If I safe this macro, there will always be this same line "30-1-07
11:53:16 0.3 0.3 7.1 0.33 0.34
1.86" in the macro. But I want to depend this search string on the actual
content of Sheet2!A1.
How would my macro have to look to accomplish this?

All in all I need a way to select a range in one sheet depending on the
varying content of a cel in another sheet.

Who can help?

Hans

 

[Bernie Deitrick]

Sub RawColumn2()
Dim myCell As Range

'This next line is not really needed for most code,
' but may be required due to how your code is written

Sheets("Sheet1").Select
Set myCell = Sheets("Sheet1").Cells.Find(What:= _
Sheets("Sheet2").Range("A1").Value _
, LookIn:=xlFormulas, LookAt:=xlPart
Sheets("Sheet1").Range(myCell, myCell.End(xlDown)).Select

'et cetera....

End Sub


HTH,
Bernie
MS Excel MVP

 

[h.]

Sub RawColumn2()

Dim myCell As Range

'This next line is not really needed for most code,
' but may be required due to how your code is written

Sheets("Sheet1").Select
Set myCell = Sheets("Sheet1").Cells.Find(What:= _
Sheets("Sheet2").Range("A1").Value _
, LookIn:=xlFormulas, LookAt:=xlPart
Sheets("Sheet1").Range(myCell, myCell.End(xlDown)).Select

'et cetera....

End Sub

Bernie,

It must be me for sure, but I keep getting an compiling error in your code
....? And I can't find what is wrong...

Hans

 

[Bernie Deitrick]

Hans,

<Smacks forehead>

I left out the close paren at the end of the Find line:

Set myCell = Sheets("Sheet1").Cells.Find(What:= _
Sheets("Sheet2").Range("A1").Value, _
LookIn:=xlFormulas, LookAt:=xlPart)

sorry about that....

HTH,
Bernie
MS Excel MVP

 

[h.]

<Smacks forehead>

thx, got it working!!!