Adding new rows in the excel after reading two cells

meysamnick

New Member
Joined
May 4, 2013
Messages
1
Hi all,

I have a tiny problem, the code below will check if ActiveCell is empty and if not it goes to the next cell below (1,0) and does something. but the clode below does both.. IF and ELSE both are executing, I don't know why! I'm just stuck.

and also I want to make the rows which is being inserted colorful and also the activecell row to be colored as well.

can someone help me out?

Code:
Sub SplitIntoSteps()

Application.ScreenUpdating = False

Range("X2").Select 'Starting position
StepNo = 1

If IsEmpty(ActiveCell) = True Then

    ActiveCell.Value = Range("U2").Value & Chr(10) & Range("V2").Value
    Range("W2").Value = "Precondition"
End If
    Do Until IsEmpty(ActiveCell) = True
    
            If InStr(ActiveCell, Chr(10)) > 0 Then
          
                    MidStartPos = InStr(ActiveCell, Chr(10))
                    ActiveCellAddress = ActiveCell.Address(False, False)
                    ActiveCellContents = ActiveCell.Value
                    ActiveCellRow = ActiveCell.Row
                    
                    ActiveCell.Offset(1, 0).Select
                    Rows(ActiveCell.Row).Insert
                    Range(ActiveCellAddress).Copy ActiveCell
                    Range("W" & ActiveCell.Row) = StepNo
                    StepNo = StepNo + 1
                        
                    Range(ActiveCellAddress).Value = Left(ActiveCellContents, MidStartPos - 1)
                    ActiveCell.Value = Mid(ActiveCellContents, MidStartPos + 1, _
                    Len(ActiveCellContents) - MidStartPos)
                    
                    Range("IV" & ActiveCellRow).End(xlToLeft).Select
                    CellAddressToCopyTo = ActiveCell.Address(False, False)
                            
                '    Range("AA" & ActiveCellRow & ":" & CellAddressToCopyTo).Copy _
                '    Range("AA" & ActiveCellRow + 1)
                    
                'move expected result
                    Range(ActiveCellAddress).Select
                    ActiveCell.Offset(1, 0).Select
                    ActiveCellContents = ActiveCell.Value
                    
                    VerifyPos = InStr(LCase(ActiveCell), "verify that")
                    'MsgBox (LCase(ActiveCell) & " " & VerifyPos)
                    
                    If VerifyPos > 0 And VerifyPos < 5 Then
                        VerifyCell = ActiveCell.Offset(-1, 1).Address(False, False)
                        MidStartPos = InStr(ActiveCell, Chr(10))
                        
                        If MidStartPos > 0 Then
                            Range(VerifyCell).Value = Left(ActiveCell.Value, MidStartPos - 1)
                            ActiveCell.Value = Mid(ActiveCellContents, MidStartPos + 1, _
                            Len(ActiveCellContents) - MidStartPos)
                            'StepNo = StepNo - 1
                        Else
                            Range(VerifyCell).Value = ActiveCell.Value
                            Rows(ActiveCell.Row).Delete
                        End If
                        Range(ActiveCellAddress).Select
                    End If
        'end move expected result
            
        '    Range(ActiveCellAddress).Select
            
        '    ActiveCell.Offset(1, 0).Select


Else
            'MsgBox Elsepassed
            ActiveCell.Offset(1, 0).Select
            If IsEmpty(ActiveCell) = True Then
                ActiveCell.Value = Range("U" & ActiveCell.Row).Value & Chr(10) & Range("V" & ActiveCell.Row).Value
                Range("W" & ActiveCell.Row).Value = "Precondition"
                'Range("W" & ActiveCell.Row) = StepNo
                'StepNo = StepNo + 1
                StepNo = 1
            End If
        End If
    Loop
'End If

Application.ScreenUpdating = True

End Sub
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Watch MrExcel Video

Forum statistics

Threads
1,123,535
Messages
5,602,217
Members
414,513
Latest member
junbuggle

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top