Correct syntax for IF, Then in a macro
Ken
I have found a macro on the net that might be the answer to my
earlier post:
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
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" <ktha...@btes.tv> skrev i meddelelsen
news:f65056ce-2d92-45ba...@z72g2000hsb.googlegroups.com...
Mike H
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
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
>
> - 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