I've got an excel spreadsheet that will contain data in up to 3 columns. If column A is populated, then either column B or column C (or both) will also be populated. If A is empty then B&C are by definition empty (even if someone makes a mistake and enters stuff there) A is a date, B&C are currency.
My VBA reads these rows and writes to a pipe delimited text file for import into another system we have. The problem I'm having is that if either B or C is null (which it frequently is) then the code just bombs out and says Type Mismatch. What I need it to do is just write a zero.
CODE --> VBA
Dim LastDataRow As Integer
Dim StartDataRow As Integer
Dim OutPutFileName As String
Dim OutPath As String
Dim FileName As String
Dim NumRows As Integer
Dim PremAmt As Currency
Dim PremDate As Date
Dim LoanAmt As Currency
'Check for Policy Number
If [b1] <> "" Then
'Obtain Number of the Last Row that has Premium Data
StartDataRow = 4 'This is always the first row that has Premium Data
LastDataRow = .Rows.Count + .Row - 1
'Declare name and location for new .txt file
'Use the policy number found on another tab to name the file
FileNum = FreeFile()
FileName = Worksheets("Sheet1").Cells(1, 2).Value
OutPath = "C:\Premium\"
OutPutFileName = OutPath & FileName & ".txt"
Open OutPutFileName For Output As #FileNum
'Determine total number of rows to write out
NumRows = LastDataRow - StartDataRow + 1
For x = 1 To NumRows
PremDate = Trim(Worksheets("Sheet1").Cells(x + StartDataRow - 1, 1).Value)
PremAmt = Trim(Worksheets("Sheet1").Cells(x + StartDataRow - 1, 2).Value)
LoanAmt = Trim(Worksheets("Sheet1").Cells(x + StartDataRow - 1, 3).Value)
Print #FileNum, PremDate & "|" & PremAmt & "|" & LoanAmt
MsgBox ("Output file has been created, go onto the next step")
Else: MsgBox ("No Policy Number Entered")