Help with Macro that contains text and not looks at case
 

I have a macro where it looks at a worksheet and if column D has specific
text it will move to a new worksheet titled"Research". What I am running into
if a user does not use the case indicated in the macro, if word is lower
case, upper case, the macro does not recognize the word and does not move the
row to "Research" worksheet, Below is the macro, does anyone have any
suggestions?

I also have two additional questions on this macro:

1) If I cut/paste instead of copy/paste to Research tab, how do I have
indicate I want the row deleted in the "TimeSheet" worksheet

2) On the MsgBox line of code, how can I indicate how many rows were moved
to the "Research worksheet"?

Thank you all for your time, trying to learn VBA, you all have been great
with your postings.

Sub CopyResearchData()
Application.ScreenUpdating = False
Range("Research!A2:Z65536").ClearContents

Dim LSearchRow As Integer
Dim LCopyToRow As Integer

On Error GoTo Err_Execute

'Start search in row 2
LSearchRow = 2

'Start copying data to row 2 in Research (row counter variable)
LCopyToRow = 2
Sheets("TimeSheet").Select

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

'If value in column D = "EMP Payroll", copy entire row to Research
If Range("D" & CStr(LSearchRow)).Value = "EMP Payroll" Then

'Select row in Data to copy
Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
Selection.Copy

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

'Move counter to next row
LCopyToRow = LCopyToRow + 1

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

End If

LSearchRow = LSearchRow + 1

Wend

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

MsgBox "Research items have been copied to the Research tab."

Exit Sub

Err_Execute:
MsgBox "An error occurred."
Application.ScreenUpdating = True
End Sub

Help with Macro that contains text and not looks at case
 

Hi,

You can use INSTR with the VBTextCompare swithch to overcome case issues,
try this line

If InStr(1, Range("D" & CStr(LSearchRow)).Value, "EMP Payroll",
vbTextCompare) 0 Then

Mike

"Jen_T" wrote:

I have a macro where it looks at a worksheet and if column D has specific
text it will move to a new worksheet titled"Research". What I am running into
if a user does not use the case indicated in the macro, if word is lower
case, upper case, the macro does not recognize the word and does not move the
row to "Research" worksheet, Below is the macro, does anyone have any
suggestions?

I also have two additional questions on this macro:

1) If I cut/paste instead of copy/paste to Research tab, how do I have
indicate I want the row deleted in the "TimeSheet" worksheet

2) On the MsgBox line of code, how can I indicate how many rows were moved
to the "Research worksheet"?

Thank you all for your time, trying to learn VBA, you all have been great
with your postings.

Sub CopyResearchData()
Application.ScreenUpdating = False
Range("Research!A2:Z65536").ClearContents

Dim LSearchRow As Integer
Dim LCopyToRow As Integer

On Error GoTo Err_Execute

'Start search in row 2
LSearchRow = 2

'Start copying data to row 2 in Research (row counter variable)
LCopyToRow = 2
Sheets("TimeSheet").Select

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

'If value in column D = "EMP Payroll", copy entire row to Research
If Range("D" & CStr(LSearchRow)).Value = "EMP Payroll" Then

'Select row in Data to copy
Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
Selection.Copy

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

'Move counter to next row
LCopyToRow = LCopyToRow + 1

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

End If

LSearchRow = LSearchRow + 1

Wend

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

MsgBox "Research items have been copied to the Research tab."

Exit Sub

Err_Execute:
MsgBox "An error occurred."
Application.ScreenUpdating = True
End Sub

Help with Macro that contains text and not looks at case
 

Another way would be to use VB's string compare (StrComp) function...

If StrComp(Range("D" & CStr(LSearchRow)).Value, "EMP Payroll",
vbTextCompare) = 0 Then

--
Rick (MVP - Excel)


"Jen_T" wrote in message
...
I have a macro where it looks at a worksheet and if column D has specific
text it will move to a new worksheet titled"Research". What I am running
into
if a user does not use the case indicated in the macro, if word is lower
case, upper case, the macro does not recognize the word and does not move
the
row to "Research" worksheet, Below is the macro, does anyone have any
suggestions?

I also have two additional questions on this macro:

1) If I cut/paste instead of copy/paste to Research tab, how do I have
indicate I want the row deleted in the "TimeSheet" worksheet

2) On the MsgBox line of code, how can I indicate how many rows were moved
to the "Research worksheet"?

Thank you all for your time, trying to learn VBA, you all have been great
with your postings.

Sub CopyResearchData()
Application.ScreenUpdating = False
Range("Research!A2:Z65536").ClearContents

Dim LSearchRow As Integer
Dim LCopyToRow As Integer

On Error GoTo Err_Execute

'Start search in row 2
LSearchRow = 2

'Start copying data to row 2 in Research (row counter variable)
LCopyToRow = 2
Sheets("TimeSheet").Select

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

'If value in column D = "EMP Payroll", copy entire row to Research
If Range("D" & CStr(LSearchRow)).Value = "EMP Payroll" Then

'Select row in Data to copy
Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
Selection.Copy

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

'Move counter to next row
LCopyToRow = LCopyToRow + 1

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

End If

LSearchRow = LSearchRow + 1

Wend

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

MsgBox "Research items have been copied to the Research tab."

Exit Sub

Err_Execute:
MsgBox "An error occurred."
Application.ScreenUpdating = True
End Sub