How do you dynamically sum up a column and print sum to text?

[vbahelpme]

I have a work assignment to convert an excel file to text with specific number of spaces between each column because our older system does accept a direct import from excel.

I have a column for gl codes and a column for amounts. here is what macro needs to print to text.

if the "amount" is negative, debit the amount, credit zeros.
if the "amount" is positive, credit the amount, debit zeros.

in the last row, i need to calculate the difference between debit and credit and balance both sides.

i'm trying to automate the process for the users so that he/she only needs to copy and paste GL codes and amounts (2 columns of data) to my macro-enabled workbook and click "convert".

So far, this is what I have:

Private Sub CommandButton1_Click()


Dim FilePath As String
Dim Debit As String
Dim Credit As String
Dim LastRow As Long
Dim tranferType As String
Dim fiscalYr As String
Dim tranDate As String

Range("A2").Select

LastRow = ActiveSheet.UsedRange.Rows.Count - 1

tranferType = InputBox("AB, BC or BU?")
If tranferType = "" Then Exit Sub
fiscalYr = InputBox("Please specify fiscal Year (201X)")
If fiscalYr = "" Then Exit Sub
tranDate = InputBox("Please specify transaction date (07/01/XX)")
If tranDate = "" Then Exit Sub

FilePath = ThisWorkbook.Path & "\Import File"
Open FilePath For Output As #2

CellData = ""
For j = 1 To LastRow

If ActiveCell(j, 2) >= 0 Then

Debit = tranferType & " " & ActiveCell(j, 1).Value & " " & Format(ActiveCell(j, 2).Value, "00000000000") & "00" & " " & "0000000000000" & " " & "FY" & fiscalYr & " Original Budget" & " " & ActiveCell(j, 3).Text & " " & tranDate
Print #2, Debit

ElseIf ActiveCell(j, 2) < 0 Then

Credit = tranferType & " " & ActiveCell(j, 1).Value & " " & "0000000000000" & " " & Format(Abs(ActiveCell(j, 2).Value), "00000000000") & "00" & " " & "FY" & fiscalYr & " Original Budget" & " " & ActiveCell(j, 3).Text & " " & tranDate
Print #2, Credit

End If

Next j

Close #2
MsgBox ("Import File Created")


End Sub

I have most of it down except calculating the difference between the debit and credit.

I am tearing my hair out trying to figure out how to total the "amounts in the column" and print it to the text. but it kept giving me zeros or some weird numbers

I'd appreciate any advice!

 

[vbahelpme]

nevermind, this is resolved

 

[Rhodie72]

I have a work assignment to convert an excel file to text with specific number of spaces between each column because our older system does accept a direct import from excel.

I have a column for gl codes and a column for amounts. here is what macro needs to print to text.

if the "amount" is negative, debit the amount, credit zeros.
if the "amount" is positive, credit the amount, debit zeros.

in the last row, i need to calculate the difference between debit and credit and balance both sides.

i'm trying to automate the process for the users so that he/she only needs to copy and paste GL codes and amounts (2 columns of data) to my macro-enabled workbook and click "convert".

So far, this is what I have:

Private Sub CommandButton1_Click()


Dim FilePath As String
Dim Debit As String
Dim Credit As String
Dim LastRow As Long
Dim tranferType As String
Dim fiscalYr As String
Dim tranDate As String

Range("A2").Select

LastRow = ActiveSheet.UsedRange.Rows.Count - 1

tranferType = InputBox("AB, BC or BU?")
If tranferType = "" Then Exit Sub
fiscalYr = InputBox("Please specify fiscal Year (201X)")
If fiscalYr = "" Then Exit Sub
tranDate = InputBox("Please specify transaction date (07/01/XX)")
If tranDate = "" Then Exit Sub

FilePath = ThisWorkbook.Path & "\Import File"
Open FilePath For Output As #2

CellData = ""
For j = 1 To LastRow

If ActiveCell(j, 2) >= 0 Then

Debit = tranferType & " " & ActiveCell(j, 1).Value & " " & Format(ActiveCell(j, 2).Value, "00000000000") & "00" & " " & "0000000000000" & " " & "FY" & fiscalYr & " Original Budget" & " " & ActiveCell(j, 3).Text & " " & tranDate
Print #2, Debit

ElseIf ActiveCell(j, 2) < 0 Then

Credit = tranferType & " " & ActiveCell(j, 1).Value & " " & "0000000000000" & " " & Format(Abs(ActiveCell(j, 2).Value), "00000000000") & "00" & " " & "FY" & fiscalYr & " Original Budget" & " " & ActiveCell(j, 3).Text & " " & tranDate
Print #2, Credit

End If

Next j

Close #2
MsgBox ("Import File Created")


End Sub

I have most of it down except calculating the difference between the debit and credit.

I am tearing my hair out trying to figure out how to total the "amounts in the column" and print it to the text. but it kept giving me zeros or some weird numbers

I'd appreciate any advice!

Have your revised code back. Without the data on sheets, it's going to be difficult. I would however suggest that you create the balances in excel first before exporting the data and then that would solve your problem because you can append that to your data...

Private Sub CommandButton1_Click()


Dim FilePath As String, Debit As String, Credit As String, LastRow As Long, _
    TransferType As String, FiscalYr As Byte, TranDate As Byte, CellData, _
    j

    Cells(2, 1).Select

    LastRow = ActiveSheet.UsedRange.Rows.Count - 1

Launch:
    On Error GoTo Launch

    TransferType = InputBox("AB, BC or BU?")
    FiscalYr = InputBox("Please specify fiscal year (201X)", "Fiscal Year", "X")
    TranDate = InputBox("Please specify transaction date (07/01/XX)")

    Select Case TransferType Or FiscalYr Or TranDate
        Case ""
            GoTo Launch
        Case Else
            TransferType = UCase(TransferType)
    End Select

On Error Resume Next
    FilePath = ThisWorkbook.Path & "\Import File"
    Open FilePath For Output As #2

    CellData = ""
        For j = 1 To LastRow

            Select Case ActiveCell(j, 2)
                Case Is >= 0 'Then
                    Debit = TransferType & " " & ActiveCell(j, 1).Value & " " & _
                            Format(ActiveCell(j, 2).Value, "00000000000") & "00" & _
                            " " & "0000000000000" & " " & "FY" & FiscalYr & _
                            " Original Budget" & " " & ActiveCell(j, 3).Text & " " & _
                            TranDate
                    Print #2, Debit

                Case Is < 0 'Then
                    Credit = TransferType & " " & ActiveCell(j, 1).Value & " " & _
                    "0000000000000" & " " & Format(Abs(ActiveCell(j, 2).Value), _
                    "00000000000") & "00" & " " & "FY" & FiscalYr & _
                    " Original Budget" & " " & ActiveCell(j, 3).Text & " " & _
                    TranDate
                    Print #2, Credit

            End Select

        Next j
        MsgBox "Import File Created"

    Close #2


End Sub