Correct syntax for IF, Then in a macro

[Ken]

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

http://groups.google.com/group/micr...s/browse_thread/thread/a7c2c08771e2ff5d?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

 

[Per Jessen]

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

 

[Mike H]

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]

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

 

[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

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

 

[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