Code Help!

[aaronRedeye]

Hi All,

This code is getting my really angry at 11:57 at night!

Can ANYONE shed any light on this?

I don't think the use of my variable within the sheet is correct so potentially that's breaking it.

Also, I'm getting a "End If with Block IF" Error message Can someone help?


'For each row in the sheet....

Do Until RowNumber > lastRow
    
    ColumnDStatus = ActiveCell.Value
    
    If ColumnDStatus = "Exists" Then Do
    
        'Condition for Exists
            Range("E" & RowNumber).Select
                LookUpresult = ActiveCell.Value
                    
                    If LookUpresult = "#N/A" Then
                       Union(Cells(RowNumber, "C"), Cells(RowNumber, "G"), Cells(RowNumber, "H")).Select
                                         
                        ManualRowCount = 2
                    
                        Selection = Selection.Value
                        Selection.Copy
                        Windows(MasterSheet).Activate
                        Sheets("Manuals").Activate
                        Range("A" & ManualRowCount).Select
                        ActiveSheet.Paste
                        ManualRowCount = ManualRowCount + 1
                        
                    Else
                    
                    [F3].Resize(, 1).EntireColumn.Insert
                        
                        Range("F" & RowNumber).Select
                        ActiveCell.Formula = "==MATCH(C9,&MasterSheet!C:C,0)"
                        Range("F" & RowNumber).Select
                        MatchResult = ActiveCell.Value
                        Windows(MasterSheet).Activate
                        Range("R:" & MatchResult).Select
                        ActiveCell.Formula = "=VLOOKUP(C & MatchResult ,&DoubleClickSheet!$C$&RowNumber:$I$&RowNumber,6,FALSE)"
                        Range("S:" & MatchResult).Select
                        ActiveCell.Formula = "=VLOOKUP(C & MatchResult ,'Sample Data.csv'!$C$&RowNumber:$I$&RowNumber,7,FALSE)"
                                                                                               
End If

        'Condition for New Row       
    ElseIf ColumnDStatus = "New Row" Then Do
                            
                      [F3].Resize(, 1).EntireColumn.Insert
'Still working on this part so pls i
 
    End If
        
'End If
    
    'Increment the row
    
    RowNumber = RowNumber + 1
    
    'Select the next cell in the sheet..
    
    Range("D" & RowNumber).Select
Loop

 

[My Aswer Is This]

It would be better if you showed us your entire script.

 

[aaronRedeye]

It would be better if you showed us your entire script.

    Public MasterSheet As String
    Public DoubleClickSheet As String
    Sub OpenDCSheet()
    
    Dim OpenFileName As String
    Dim wb As Workbook
    Dim lastRow As Long
        
    MasterSheet = ActiveWorkbook.Name

    
    'Select and Open workbook
    MsgBox ("Please select the data file")
        OpenFileName = Application.GetOpenFilename
            If OpenFileName = "False" Then Exit Sub
                 Set wb = Workbooks.Open(OpenFileName, UpdateLinks = 0)
    
    DoubleClickSheet = ActiveWorkbook.Name
     Windows(DoubleClickSheet).Activate
     
     'Define The Last row
     lastRow = Range("A" & Rows.Count).End(xlUp).Row
     
    'Inserting a Column at Column B
    [A3].Resize(, 4).EntireColumn.Insert
     
    
     'Add the week commencing date
     Range("A8").Select
        ActiveCell.Formula = "=MID($E$4,3,8)"
            Range("A8").Select
                Selection.Copy
                    Range("A8:A" & lastRow).Select
                        ActiveSheet.Paste
            
     
              
   'Concatenate different parts of the cell to make a date
    Range("B8").Select
        ActiveCell.Formula = "=CONCATENATE(Mid(A8, 7, 2), ""/"", Mid(E8, 5, 2), ""/"", MID(E8,3,2))"
            Range("B8").Select
                Selection.Copy
                    Range("B8:B" & lastRow).Select
                        ActiveSheet.Paste
    
    
    'Concatenate and make a unique string
    Range("C8").Select
        ActiveCell.Formula = "=CONCATENATE(B8,"" "",E8)"
    Range("C8").Select
    Selection.Copy
    Range("C8:C" & lastRow).Select
    ActiveSheet.Paste
    
    'If statement to determine if the date exists
    
    Range("D8").Select
        ActiveCell.Formula = "=IF(LEFT(E8,8)-A8>=0,""Exists"",""New Row"")"
            Range("D8").Select
                Selection.Copy
                    Range("D8:D" & lastRow).Select
                        ActiveSheet.Paste
    'Replace Underscores with spaces
    Columns("E").Replace _
         What:="_", Replacement:=" ", _
            SearchOrder:=xlByColumns, MatchCase:=True
            
  'Paste If formula results as values
    Range("D8:D" & lastRow).Select
    Selection = Selection.Value
    Selection.Copy
    Range("D8:D" & lastRow).Select
   ActiveSheet.Paste


'Delete any rows that return a #VALUE
            With ActiveSheet
    .AutoFilterMode = False
    With Range("d1", Range("d" & Rows.Count).End(xlUp))
        .AutoFilter 1, "#VALUE!"
        On Error Resume Next
        .Offset(1).SpecialCells(12).EntireRow.Delete
    End With
    .AutoFilterMode = False
End With

'Redefine lastRow
lastRow = Range("A" & Rows.Count).End(xlUp).Row




 [E3].Resize(, 1).EntireColumn.Insert
 
 Range("E8").Select
 ActiveCell.Formula = "=VLOOKUP(C8,'[Master file.xls]2014 week'!$C:$C,1,FALSE)"
 Range("E8").Select
 Selection.Copy
 Range("E8:E" & lastRow).Select
 ActiveSheet.Paste
 
 Range("E8:E" & lastRow).Select
 Selection = Selection.Value
 Selection.Copy
 
 Range("E8:E" & lastRow).Select
 ActiveSheet.Paste
 
'If exists column = "Exists" then do normal vlookup on the


Dim RowNumber As Integer
Dim ColumnDStatus As String
Dim SearchValue As String
Dim LookUpresult As String
Dim ManualRowCount As Integer
Dim MatchResult As Integer

Range("D8").Select
RowNumber = Row.Count

'Find SearchValue in Master data sheet and vlookup
'ELSE Insert new row, add UNQ.String to New row then do the vlookup
'Loop untill using Rows.count + 1 and lastRow (Already declared)

'For each row in the sheet....

Do Until RowNumber > lastRow
    
    ColumnDStatus = ActiveCell.Value
    
    If ColumnDStatus = "Exists" Then Do
    
        'Condition for Exists
            Range("E" & RowNumber).Select
                LookUpresult = ActiveCell.Value
                    
                    If LookUpresult = "#N/A" Then
                       Union(Cells(RowNumber, "C"), Cells(RowNumber, "G"), Cells(RowNumber, "H")).Select
                                         
                        ManualRowCount = 2
                    
                        Selection = Selection.Value
                        Selection.Copy
                        Windows(MasterSheet).Activate
                        Sheets("Manuals").Activate
                        Range("A" & ManualRowCount).Select
                        ActiveSheet.Paste
                        ManualRowCount = ManualRowCount + 1
                        
                    Else
                    
                    [F3].Resize(, 1).EntireColumn.Insert
                        
                        Range("F" & RowNumber).Select
                        ActiveCell.Formula = "==MATCH(C9,&MasterSheet!C:C,0)"
                        Range("F" & RowNumber).Select
                        MatchResult = ActiveCell.Value
                        Windows(MasterSheet).Activate
                        Range("R:" & MatchResult).Select
                        ActiveCell.Formula = "=VLOOKUP(C & MatchResult ,&DoubleClickSheet!$C$&RowNumber:$I$&RowNumber,6,FALSE)"
                        Range("S:" & MatchResult).Select
                        ActiveCell.Formula = "=VLOOKUP(C & MatchResult ,'Sample Data.csv'!$C$&RowNumber:$I$&RowNumber,7,FALSE)"
                                                                                               
End If
        
    ElseIf ColumnDStatus = "New Row" Then Do
                            
                      [F3].Resize(, 1).EntireColumn.Insert
                      
                      
                        
             
        
    
    
        'Condition for New Row
                    
                        
                

     'Condition for Else
    End If
        
'End If
    
    'Increment the row
    
    RowNumber = RowNumber + 1
    
    'Select the next cell in the sheet..
    
    Range("D" & RowNumber).Select
Loop



    'Windows(MasterSheet).Activate
    
                             
                             
    End Sub