Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing multiple cell text from lower case to upper case | Excel Discussion (Misc queries) | |||
vlookup -- Upper case and Lower case text | Excel Discussion (Misc queries) | |||
Change the text from lower case to upper case in an Excel work boo | Excel Discussion (Misc queries) | |||
Macro for changing text to Proper Case | Excel Worksheet Functions | |||
How do I change existing text from lower case to upper case | Excel Discussion (Misc queries) |