Hi group,
I have found a macro on the net that might be the answer to my
earlier post:

http://groups.google.com/group/micro...71e2ff5d?hl=en

if I can resolve this IF, Then statement syntax:

'If value in column J = LSearchValue, and column O or Q are empty,
copy entire row to Sheet2
If Range("J" & CStr(LSearchRow)).Value = LSearchValue &
Range("O").Value = "" & Range("Q").Value = ""
Then

I know that's not the correct way to do that, but can anyone tell me
what needs to be changed? Any guidance will be very much appreciated!!
Ken

Hi Ken

Try this:

'If value in column J = LSearchValue, and column O or Q are empty, copy
entire row to Sheet2
tRow = CStr(LSearchRow)
If Cells(tRow, "J").Value = LSearchValue Then
If Cells(tRow, "O").Value = "" Or Cells(tRow, "Q").Value = "" Then
Rows(tRow).Copy Destination:=Sheets("Sheet2").Range("A1")
End If
End If

Regards,
Per

"Ken" skrev i meddelelsen
...
Hi group,
I have found a macro on the net that might be the answer to my
earlier post:

http://groups.google.com/group/micro...71e2ff5d?hl=en

if I can resolve this IF, Then statement syntax:

'If value in column J = LSearchValue, and column O or Q are empty,
copy entire row to Sheet2
If Range("J" & CStr(LSearchRow)).Value = LSearchValue &
Range("O").Value = "" & Range("Q").Value = ""
Then

I know that's not the correct way to do that, but can anyone tell me
what needs to be changed? Any guidance will be very much appreciated!!
Ken

Hi,

Right click the sheet tab of the sheet you are saerching, view code and
paste this in and run it

Sub copyit()
searchvalue = "Something"
Dim myrange, MyRange1 As Range

lastrow = Cells(Rows.Count, "J").End(xlUp).Row
Set myrange = Range("J1:J" & lastrow)
For Each c In myrange
If c.Value = searchvalue And IsEmpty(c.Offset(, 5)) And
IsEmpty(c.Offset(, 7)) Then
If MyRange1 Is Nothing Then
Set MyRange1 = c.EntireRow
Else
Set MyRange1 = Union(MyRange1, c.EntireRow)
End If
End If
Next
If Not MyRange1 Is Nothing Then
MyRange1.Copy
Sheets("Sheet2").Range("A1").PasteSpecial
End If

End Sub

Mike

"Ken" wrote:

Hi group,
I have found a macro on the net that might be the answer to my
earlier post:

http://groups.google.com/group/micro...71e2ff5d?hl=en

if I can resolve this IF, Then statement syntax:

'If value in column J = LSearchValue, and column O or Q are empty,
copy entire row to Sheet2
If Range("J" & CStr(LSearchRow)).Value = LSearchValue &
Range("O").Value = "" & Range("Q").Value = ""
Then

I know that's not the correct way to do that, but can anyone tell me
what needs to be changed? Any guidance will be very much appreciated!!
Ken

Thanks Mike and Per....I really appreciate your help.....looking at
your suggestions, here is what I came up with that seems so far to do
the trick:
Sub SearchForString2()

Dim LSearchRow As Integer
Dim LCopyToRow As Integer
Dim LSearchValue As String

On Error GoTo Err_Execute

LSearchValue = InputBox("Please enter a value to search for.DO NOT
hit OK or CANCEL before entering a date, entire log will transfer!!!",
"Enter value")

'Start search in row 2
LSearchRow = 2

'Start copying data to row 4 in WeeklyDueLog (row counter
variable)
LCopyToRow = 4

While Len(Range("A" & CStr(LSearchRow)).Value) 0

'If value in column J = LSearchValue, and column O or Q are empty,
copy entire row to Sheet2

If Cells(LSearchRow, "J").Value = LSearchValue And
Cells(LSearchRow, "O").Value = "" And Cells(LSearchRow, "Q").Value =
"" Then
'Select row in JobLogEntry to copy
Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
Selection.Copy

'Paste row into WeeklyDueLog in next row
Sheets("WeeklyDueLog").Select
Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
ActiveSheet.Paste

'Move counter to next row
LCopyToRow = LCopyToRow + 1

'Go back to JobLogEntry to continue searching
Sheets("JobLogEntry").Select

End If

LSearchRow = LSearchRow + 1

Wend

'Position on cell A3
Application.CutCopyMode = False
Range("A3").Select

MsgBox "All matching data has been copied."

Exit Sub

Err_Execute:
MsgBox "An error occurred."

End Sub

The only question I have right now, is how would I disable the OK and
Cancel button on my input box until an input is actually entered?? If
I hit either one before entering the search date, the entire log
(5000+ entries) transfers....sort of like a "wildcard" entry...if you
have any suggestions, I am deeply grateful...if not, I am still
thankful for your help!
Ken

On Jun 21, 11:12*am, Ken wrote:
Thanks Mike and Per....I really appreciate your help.....looking at
your suggestions, here is what I came up with that seems so far to do
the trick:
Sub SearchForString2()

* * Dim LSearchRow As Integer
* * Dim LCopyToRow As Integer
* * Dim LSearchValue As String

* * On Error GoTo Err_Execute

* * LSearchValue = InputBox("Please enter a value to search for.DO NOT
hit OK or CANCEL before entering a date, entire log will transfer!!!",
"Enter value")

* * 'Start search in row 2
* * LSearchRow = 2

* * 'Start copying data to row 4 in WeeklyDueLog (row counter
variable)
* * LCopyToRow = 4

* * While Len(Range("A" & CStr(LSearchRow)).Value) 0

* * 'If value in column J = LSearchValue, and column O or Q are empty,
copy entire row to Sheet2

* * If Cells(LSearchRow, "J").Value = LSearchValue And
Cells(LSearchRow, "O").Value = "" And Cells(LSearchRow, "Q").Value =
"" Then
* * * * * * 'Select row in JobLogEntry to copy
* * * * * * Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
* * * * * * Selection.Copy

* * * * * * 'Paste row into WeeklyDueLog in next row
* * * * * * Sheets("WeeklyDueLog").Select
* * * * * * Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
* * * * * * ActiveSheet.Paste

* * * * * * 'Move counter to next row
* * * * * * LCopyToRow = LCopyToRow + 1

* * * * * * 'Go back to JobLogEntry to continue searching
* * * * * * Sheets("JobLogEntry").Select

* * * * End If

* * * * LSearchRow = LSearchRow + 1

* * Wend

* * 'Position on cell A3
* * Application.CutCopyMode = False
* * Range("A3").Select

* * MsgBox "All matching data has been copied."

* * Exit Sub

Err_Execute:
* * MsgBox "An error occurred."

End Sub

The only question I have right now, is how would I disable the OK and
Cancel button on my input box until an input is actually entered?? If
I hit either one before entering the search date, the entire log
(5000+ entries) transfers....sort of like a "wildcard" entry...if you
have any suggestions, I am deeply grateful...if not, I am still
thankful for your help!
Ken

Correction on the last statement, all entries with no due date are
found, and that in itself is OK.....Thanks guys for your help....Ken

On Jun 21, 11:20*am, Ken wrote:
On Jun 21, 11:12*am, Ken wrote:





Thanks Mike and Per....I really appreciate your help.....looking at
your suggestions, here is what I came up with that seems so far to do
the trick:
Sub SearchForString2()

* * Dim LSearchRow As Integer
* * Dim LCopyToRow As Integer
* * Dim LSearchValue As String

* * On Error GoTo Err_Execute

* * LSearchValue = InputBox("Please enter a value to search for.DO NOT
hit OK or CANCEL before entering a date, entire log will transfer!!!",
"Enter value")

* * 'Start search in row 2
* * LSearchRow = 2

* * 'Start copying data to row 4 in WeeklyDueLog (row counter
variable)
* * LCopyToRow = 4

* * While Len(Range("A" & CStr(LSearchRow)).Value) 0

* * 'If value in column J = LSearchValue, and column O or Q are empty,
copy entire row to Sheet2

* * If Cells(LSearchRow, "J").Value = LSearchValue And
Cells(LSearchRow, "O").Value = "" And Cells(LSearchRow, "Q").Value =
"" Then
* * * * * * 'Select row in JobLogEntry to copy
* * * * * * Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow))..Select
* * * * * * Selection.Copy

* * * * * * 'Paste row into WeeklyDueLog in next row
* * * * * * Sheets("WeeklyDueLog").Select
* * * * * * Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow))..Select
* * * * * * ActiveSheet.Paste

* * * * * * 'Move counter to next row
* * * * * * LCopyToRow = LCopyToRow + 1

* * * * * * 'Go back to JobLogEntry to continue searching
* * * * * * Sheets("JobLogEntry").Select

* * * * End If

* * * * LSearchRow = LSearchRow + 1

* * Wend

* * 'Position on cell A3
* * Application.CutCopyMode = False
* * Range("A3").Select

* * MsgBox "All matching data has been copied."

* * Exit Sub

Err_Execute:
* * MsgBox "An error occurred."

End Sub

The only question I have right now, is how would I disable the OK and
Cancel button on my input box until an input is actually entered?? If
I hit either one before entering the search date, the entire log
(5000+ entries) transfers....sort of like a "wildcard" entry...if you
have any suggestions, I am deeply grateful...if not, I am still
thankful for your help!
Ken

Correction on the last statement, all entries with no due date are
found, and that in itself is OK.....Thanks guys for your help....Ken- Hide quoted text -

- Show quoted text -

I only have one problem, though, the dates in the copied row show up
as it's numeric equivalent and cannot be changed, even with all the
usual cell formatting tools....any ideas on what is happening? Can it
be changed in the macro??
Ken