need to create a VB loop and VB forumula to incorporate this cell formula in a 2 sheet excel file

[pbcnick]

I posted this in reply to a thread, but I think I should have opened a new thread here to get more exposure.


Can I get some help with a formula ? I am very new to Macros.. like 1 day new.


I worked on this all day yesterday at work. It's a two part process. additionally, i'd like to wrap this in a loop, so if it's shorter than 500 rows (as I mention a few lines down, this is what I set it to) it will stop.. and if the file is larger than 500 rows i will process the entire file.


Sheet 1 has 3 columns. An account ID in col A. Amount to be paid in column B, and the name of the Payee in column C.
I need it to work for 500 rows of data. (EDIT: if this was encapsulated in a loop that would be amazing)

right now i can do this process manually, if I copy/paste formulas and copy down.. but can't get it recorded in a macro.
i am copying/pasting the forumulas in the cells while recording the macro. that doesn't seem to always work.


on column D, I have it to copy the amount from B and remove '$' and '.' while retaining any trailing zeros.. i.e. $1745.50 in column B shows as 174550 in column D. The formula I'm using works..

=SUBSTITUTE(TEXT(B1,"0.00"),".","")

Sheet2
uses this formula (which I put together from hacking up a few different things).

=Sheet1!A1&"         "&REPT(0,16-LEN(Sheet1!D1))&FIXED(Sheet1!D1,0,1)&"EC"&Sheet1!C1

this formula also works for my needs.


the problem I'm having is that my MACRO always works differently. It seems to be using info from my clipboard. Like I said, I'm new to this so it's a steep learning curve right now.


my expected output is a text file with format as follows:
(account#)(9spaces)( 16 digits to include paid amount with leading 0's to fill in the 16 character 'payload')(2 digit import code)(name of payee)

a sample output provided below for a sheet with column A=5113020, column B= $1,693.32, col c = PAYEENAMEGOESHERE

5113020         0000000000169332WIPAYEENAMEGOESHERE

when I noticed it was using my clipboard and i looked at this below, there are not any formulas in the Macro.
I would like to insert the above formulas so it works on any pc.

CURRENT MACRO

Sub SimpleConverterXL2TXT()
'
' SimpleConverterXL2TXT Macro
' testing
'
'
    ActiveSheet.Paste
    Range("D2").Select
    Sheets("Sheet2").Select
    ActiveSheet.Paste
    Sheets("Sheet1").Select
    Range("D1").Select
    Selection.AutoFill Destination:=Range("D1:D500"), Type:=xlFillDefault
    Range("D1:D500").Select
    Sheets("Sheet2").Select
    Selection.AutoFill Destination:=Range("A1:A500"), Type:=xlFillDefault
    Range("A1:A500").Select
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Users\myusernamegoeshere\Desktop\BatchProcessImportfile.txt", FileFormat:= _
        xlTextMSDOS, CreateBackup:=False
End Sub

I'd like to insert the actual formulas I provided above.
can you assist to convert it and maybe even clue me into where it goes on this script/macro?


I promise when I achieve hero status to mention this website!


PS> I've tried to start on my own, and I'm getting an error right off the bat at the first 'formula' command.

This is totally hacked up, i'm just getting desperate.

    Sheets("Sheet1").Select
    Range("D1").Formula "=SUBSTITUTE(TEXT(B1,""0.00""),""."","""")"
   Sheets("Sheet2").Select
   Range("A1").Formula "=Sheet1!A1&""         ""&REPT(0,16-LEN(Sheet1!D1))&FIXED(Sheet1!D1,0,1)&""EC""&Sheet1!C1"

thanks in advance for any time spent looking into this.

 

[pbcnick]

ah man.. i was missing the = sign!

    Sheets("Sheet1").Select
    Range("D1").Formula "=SUBSTITUTE(TEXT(B1,""0.00""),""."","""")"
   Sheets("Sheet2").Select
   Range("A1").Formula "=Sheet1!A1&""         ""&REPT(0,16-LEN(Sheet1!D1))&FIXED(Sheet1!D1,0,1)&""EC""&Sheet1!C1"

instead of

    Sheets("Sheet1").Select
    Range("D1").Formula = "=SUBSTITUTE(TEXT(B1,""0.00""),""."","""")"
   Sheets("Sheet2").Select
   Range("A1").Formula = "=Sheet1!A1&""         ""&REPT(0,16-LEN(Sheet1!D1))&FIXED(Sheet1!D1,0,1)&""EC""&Sheet1!C1"

ok, i'm still fiddling with the rest,.

soooo if anyone wants to help me?!

thanks

nick

 

[pbcnick]

I'm getting closer.

i have the loop setup and its doing down the rows and stopping at the end.

it's still a link funky. I can't get this piece of move down each row. my syntax is off.
can you help..

this needs tweaking..

"=SUBSTITUTE(TEXT(B1,""0.00""),""."","""")"

and so does this.

"=Sheet1!A1&""         ""&REPT(0,16-LEN(Sheet1!D1))&FIXED(Sheet1!D1,0,1)&""EC""&Sheet1!C1"

here is all the code so far. thank in advance for any help.

Sub SimpleConverterXL2TXT2()
'
' SimpleConverterXL2TXT Macro
' testing
'


'
    Counter = 1
    
   
      Do
    
        Sheets("Sheet1").Select
    
    Range("A1").Offset(Counter, 0).Formula = "=SUBSTITUTE(TEXT(B1,""0.00""),""."","""")"
        Sheets("Sheet2").Select
    Range("A1").Offset(Counter).Formula = "=Sheet1!A1&""         ""&REPT(0,16-LEN(Sheet1!D1))&FIXED(Sheet1!D1,0,1)&""EC""&Sheet1!C1"
     


Sheets("Sheet1").Select
Range("A1").Offset(Counter, 0).Select


Counter = Counter + 1






    Loop Until IsEmpty(ActiveCell.Offset(1, 0))
     
Sheets("Sheet2").Select
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Users\usernamegoeshere\Desktop\BatchProcessImportfile.txt", FileFormat:= _
        xlTextMSDOS, CreateBackup:=False
End Sub

 

[pbcnick]

well I finally got this done. this is my first VB macro. i didn't know anything about this 2 days ago.
the forumlas i started with are hacked up from google searches.
figured I'd post my resoultion, in case the syntax helps someone at some time..
and yes, the counter isn't really a counter, but thats the name i used.
although I think no one will ever see this!

Sub XL2TXT1()
'
' XL2TXT1 Macro
' working at home!
'
    Counter = 1
     
      Do

       Sheets("Sheet1").Select
       Cells(Counter, 4).Select
       ActiveCell.Formula = "=SUBSTITUTE(TEXT(B" & Counter & ",""0.00""),""."","""")"
       
        Sheets("Sheet2").Select
        Cells(Counter, 1).Select

' I used cell references and replaced the row number with double quote space ampersand variable named counter space ampersand double quote
        ActiveCell.Formula = "=Sheet1!A" & Counter & "&""         ""&REPT(0,16-LEN(Sheet1!D" & Counter & "))&FIXED(Sheet1!D" & Counter & ",0,1)&""EC""&Sheet1!C" & Counter & ""
         
' bring it back to sheet 1 to the next row to check for last row.
Sheets("Sheet1").Select
Range("A1").Offset(Counter, 0).Select
 
' not really a counter, just used to skip down to the next row
Counter = Counter + 1
  
    Loop Until IsEmpty(ActiveCell.Offset(0, 0))
    
Sheets("Sheet2").Select
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Users\PCusernamegoeshere\Desktop\BatchProcessImportfile.txt", FileFormat:=xlTextMSDOS, _
        CreateBackup:=False
End Sub

here is one row of final output

19-370-2010         0000000001186393ECNAME OF PAYEE