Dynamic CSV Creation

sahaider

New Member
Joined
May 30, 2014
Messages
30
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:

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
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

rollis13

Active Member
Joined
Jul 30, 2012
Messages
415
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
May 30, 2014
Messages
30
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
VBA Code:

how do i use the IFNA formula in VBA?
 

rollis13

Active Member
Joined
Jul 30, 2012
Messages
415
Office Version
  1. 2016
Platform
  1. Windows
IFNA is to be used within the formulas, not in VBA.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,615
Messages
5,625,872
Members
416,141
Latest member
Bartek9q

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