Dynamic CSV Creation

[sahaider]

I have a macro that opens a CSV file in the background and then copies data from that csv file and paste the contents in two columns of sheet1 called Index and Class
There are some formulaes defined for columns 3 to 7 of Sheet1. The pasted contents in column Index and Class can be dynamic so sometimes the defined formulaes in
column 3 to 7 will return #N/A for some rows near the end.

What i would like to have is a sub-procedure that will be called after paste operation is completed.
This procedure will only write contents of col 1 and col 7 of Sheet1 as a separate CSV without the #N/A present in the output CSV. So all the #N/A that has resulted due to VLOOKUP fail will be ignored in the final CSV.
Final CSV should only have Contents of Column Index and PCI .User should be prompted to give a name of the new CSV.


here is the code i have done so far:

Private Sub Sheet1_Click()

Dim lastrow As Integer, intR1, intR2 As Integer

'Clearing old contents
With Worksheets("Sheet1")
      .Range("A2", .Range("B" & Rows.Count).End(xlUp)).ClearContents
   End With
    
    
    'Opening CSV file,copying and pasting its contents and closing CSV

FileToOpen = Application.GetOpenFilename(Title:="Browse for the Sheet1 data", fileFilter:="CSV Files(*.csv),*csv*")
    If FileToOpen <> False Then
    Set Openbook = Application.Workbooks.Open(FileToOpen)
    
        With Openbook.Sheets(1)
            .Range("A4", .Range("B" & Rows.Count).End(xlUp)).Copy
        End With

    ThisWorkbook.Worksheets("Sheet1").Range("A2").PasteSpecial xlPasteValues
    Openbook.Close False
    
       
   End If
   
     Application.ScreenUpdating = True
     
     'Prompting user to write contents of column 1 and 7 as a new CSV
     
   intR1 = MsgBox("Sheet1 Data updated, do you want to create CSV file with COL1 1 and 7 data?", vbYesNoCancel, "CSV Generator")
     
   If intR1 = vbYes Then
   
   'Call sub-procedure to write new CSV
   
   Write_CSV
   
   
   Else
   
MsgBox "Data not required"
End If
End Sub

 

[rollis13]

Since you didn't show your formula I'll give a blind suggestion:
Why not use function IFNA( value, value_if_na ), just add =IFNA( in front of your formula and complete the new formula with the required argument.

 

[sahaider]

Since you didn't show your formula I'll give a blind suggestion:
Why not use function IFNA( value, value_if_na ), just add =IFNA( in front of your formula and complete the new formula with the required argument.

Hello:

Here is the code:
VBA Code:
Private Sub Sheet1_Click()

Dim lastrow As Integer, intR1, intR2 As Integer

'Clearing old contents
With Worksheets("Sheet1")
.Range("A2", .Range("B" & Rows.Count).End(xlUp)).ClearContents
End With


'Opening CSV file,copying and pasting its contents and closing CSV

FileToOpen = Application.GetOpenFilename(Title:="Browse for the Sheet1 data", fileFilter:="CSV Files(*.csv),*csv*")
If FileToOpen <> False Then
Set Openbook = Application.Workbooks.Open(FileToOpen)

With Openbook.Sheets(1)
.Range("A4", .Range("B" & Rows.Count).End(xlUp)).Copy
End With

ThisWorkbook.Worksheets("Sheet1").Range("A2").PasteSpecial xlPasteValues
Openbook.Close False


End If

Application.ScreenUpdating = True

'Prompting user to write contents of column 1 and 7 as a new CSV

intR1 = MsgBox("Sheet1 Data updated, do you want to create CSV file with COL1 1 and 7 data?", vbYesNoCancel, "CSV Generator")

If intR1 = vbYes Then

'Call sub-procedure to write new CSV

Write_CSV


Else

MsgBox "Data not required"
End If
End Sub

how do i use the IFNA formula in VBA?

 

[rollis13]

IFNA is to be used within the formulas, not in VBA.