Find & Replace in Excel VBA

mike637
mike637 used Ask the Experts™
on
Hello Experts

I have my code written to copy the necessary information from another workbook -
(I have an example of that data in the attachment on Sheet2.

After I copy this data, I massage it a bit, but I am having problems writing additional code to find in range("Z33:AF38") any "0 -" and replace with a "12 -" then find any cells with "- 0" and change to "- 12".  
I need to look in values, without matching the entire cell contents. I need to find a way for it to not replace a cell with "10 -" (a double digit integer) otherwise my data wil be incorrect.

Also -

I have a routine called "formatschedule" - I had to type it all out since I was stuck on the best way to set my varibles to copy range("Z13:AM24") to range ("Z33:AF38") with the modification of hours and minutes.  If you could educate me on how to get it to be reduced down, it will help me + plus, if I have to make any changes in the future I do not have to modify so much code.

I am attaching a copy of a workbook with my sheets and code. All code is in Module 1. I think this will help in showing what I am trying to do.

I would appreciate any assistance on these 2 issues.

Thanks,
Michael
Example.xlsm
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Martin Liss"Life would be infinitely happier if we could only be born at the age of eighty and gradually approach eighteen." - Mark Twain
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
For the first part ake the cell value and split it like this, replacing my 'CellValue' with your range().Value

Dim strParts() As String

strParts = Split(CellValue, "0 -")
If Len(strParts(0)) = 0 Then
    MsgBox "12 - " & strParts(1)
End If

Author

Commented:
Hello Experts,

I understand the split function - but I am confused how to identify the cell in my range and replace the "0" with "12".  I tried to use the solution, but kept getting a mismatch error doing testing.  I tried a few things but no luck.

Can you lay it out how to
set Range("AB35:AF38") and then check each cel in range and if Len(split(0 placement)) = "0" then replace.

Sorry - but she is beating me down, and winning.
Michael
Martin Liss"Life would be infinitely happier if we could only be born at the age of eighty and gradually approach eighteen." - Mark Twain
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Dim cell
Dim strParts() As String

For Each cell In Range("Z33:AF38")
    strParts = Split(cell.value, "0 -")
    If Len(strParts(0)) = 0 Then
        cell.Value = "12 - " & strParts(1)
    End If
Next
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

replace your FormatSchedule with my version,
Seems to give the desired outcome.
Private Sub FormatSchedule2()
    Dim xCell As Range, xOut As Range, outVal As String
    Set xCell = Range("Y11")  'the start
    Set xOut = Range("Y32")
    'copy the headings

    Do While xCell.Offset(2, 0).Value <> ""  'there is another user
        Set xCell = xCell.Offset(2, 0)
        Set xOut = xOut.Offset(1, 0)
        xOut.Offset(0, 0).Value = xCell.Value
        'now do hours
        For i = 1 To 7
            If xCell.Offset(0, i * 2 - 1).Value = "X" Or xCell.Offset(0, i * 2).Value = "X" Then
                outVal = ""
             Else
                stval = Hour(xCell.Offset(0, i * 2 - 1))
                If stval > 12 Then stval = stval - 12
                If Minute(xCell.Offset(0, i * 2 - 1).Value) > 0 Then
                    stval = stval & ":" & Minute(xCell.Offset(0, i * 2 - 1).Value) / 10
                End If
                If xCell.Offset(0, i * 2).Value = "cl" Then
                    endval = "cl"
                 Else
                    endval = Hour(xCell.Offset(0, i * 2))
                    If endval > 12 Then endval = endval - 12
                    If Minute(xCell.Offset(0, i * 2).Value) > 0 Then
                        endval = endval & ":" & Minute(xCell.Offset(0, i * 2).Value) / 10
                    End If
                End If
                outVal = "'" & stval & " - " & endval
            End If
            xOut.Offset(0, i).Value = outVal
        Next i
        'the total
        xOut.Offset(0, 8).Value = xCell.Offset(0, 15).Value
    Loop
            
        
        
End Sub

Open in new window


and will work if you add staff members.
Martin Liss"Life would be infinitely happier if we could only be born at the age of eighty and gradually approach eighteen." - Mark Twain
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
Private Sub FormatSchedule()
   
    Dim i As Long
    Dim j As Long
   
    j = 20
    For i = 33 To 38
        Range("Y" & i).FormulaR1C1 = "=R[-" & j & "]C"
       
        Range("Z" & i).Select
        ActiveCell.FormulaR1C1 = _
            "=MOD(HOUR(R[-" & j & "]C),12)&IF(MINUTE(R[-" & j & "]C)=0,"""","":""&(MINUTE(R[-" & j & "]C)/10))&"" - ""&IF((R[-" & j & "]C[1])=""cl"",""cl"",MOD(HOUR(R[-" & j & "]C[1]),12)&IF(MINUTE(R[-" & j & "]C[1])=0,"""","":""&(MINUTE(R[-" & j & "]C[1])/10)))"
        Range("AA" & i).Select
        ActiveCell.FormulaR1C1 = _
            "=MOD(HOUR(R[-" & j & "]C[1]),12)&IF(MINUTE(R[-" & j & "]C[1])=0,"""","":""&(MINUTE(R[-" & j & "]C[1])/10))&"" - ""&IF((R[-" & j & "]C[2])=""cl"",""cl"",MOD(HOUR(R[-" & j & "]C[2]),12)&IF(MINUTE(R[-" & j & "]C[2])=0,"""","":""&(MINUTE(R[-" & j & "]C[2])/10)))"
        Range("AB" & i).Select
        ActiveCell.FormulaR1C1 = _
            "=MOD(HOUR(R[-" & j & "]C[2]),12)&IF(MINUTE(R[-" & j & "]C[2])=0,"""","":""&(MINUTE(R[-" & j & "]C[2])/10))&"" - ""&IF((R[-" & j & "]C[3])=""cl"",""cl"",MOD(HOUR(R[-" & j & "]C[3]),12)&IF(MINUTE(R[-" & j & "]C[3])=0,"""","":""&(MINUTE(R[-" & j & "]C[3])/10)))"
        Range("AC" & i).Select
        ActiveCell.FormulaR1C1 = _
            "=MOD(HOUR(R[-" & j & "]C[3]),12)&IF(MINUTE(R[-" & j & "]C[3])=0,"""","":""&(MINUTE(R[-" & j & "]C[3])/10))&"" - ""&IF((R[-" & j & "]C[4])=""cl"",""cl"",MOD(HOUR(R[-" & j & "]C[4]),12)&IF(MINUTE(R[-" & j & "]C[4])=0,"""","":""&(MINUTE(R[-" & j & "]C[4])/10)))"
        Range("AD" & i).Select
        ActiveCell.FormulaR1C1 = _
            "=MOD(HOUR(R[-" & j & "]C[4]),12)&IF(MINUTE(R[-" & j & "]C[4])=0,"""","":""&(MINUTE(R[-" & j & "]C[4])/10))&"" - ""&IF((R[-" & j & "]C[5])=""cl"",""cl"",MOD(HOUR(R[-" & j & "]C[5]),12)&IF(MINUTE(R[-" & j & "]C[5])=0,"""","":""&(MINUTE(R[-" & j & "]C[5])/10)))"
        Range("AE" & i).Select
        ActiveCell.FormulaR1C1 = _
            "=MOD(HOUR(R[-" & j & "]C[5]),12)&IF(MINUTE(R[-" & j & "]C[5])=0,"""","":""&(MINUTE(R[-" & j & "]C[5])/10))&"" - ""&IF((R[-" & j & "]C[6])=""cl"",""cl"",MOD(HOUR(R[-" & j & "]C[6]),12)&IF(MINUTE(R[-" & j & "]C[6])=0,"""","":""&(MINUTE(R[-" & j & "]C[6])/10)))"
        Range("AF" & i).Select
        ActiveCell.FormulaR1C1 = _
            "=MOD(HOUR(R[-" & j & "]C[6]),12)&IF(MINUTE(R[-" & j & "]C[6])=0,"""","":""&(MINUTE(R[-" & j & "]C[6])/10))&"" - ""&IF((R[-" & j & "]C[7])=""cl"",""cl"",MOD(HOUR(R[-" & j & "]C[7]),12)&IF(MINUTE(R[-" & j & "]C[7])=0,"""","":""&(MINUTE(R[-" & j & "]C[7])/10)))"
        Range("AG" & i).FormulaR1C1 = "=R[-" & j & "]C[7]"

        j = j - 1
    Next
   
   
End Sub

Author

Commented:
Thank you Experts - the best solution was robberbaron - it actually saved some additonal code to remove errors and it corrected the flaw in the original code that initiated the first part of my question of [find & replace].  It has saved a lot of work and extra steps and code.

I admire your work Experts - and it really helps me in a time-crunch period.

Thank you again robberbaron!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial