Specific Macros

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Biochemist - 08 Dec 2007 16:33 GMT

I would like to know how to write a specific macro that handles the following
format:
download a series of alpha and numbers, the alpha values are always second
in the location, example would be
Cell A1 = 10102007 truckstop $15.32 $4449.31
Cell A2 = 95102007 office stop store center $22.32 $3465.89
I would like to have the macro able to place dates into A column dates, B
column vendor name, C column the first $ value, and in the D column the last
$ value, and have this run for the complete spreadsheet.
Thanks in advance

Reply to this Message

Bob Phillips - 08 Dec 2007 17:53 GMT

Turn the macro recorder on, do a Data>Text To Columns with a space
delimiter, this will give you the code.

Signature

---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

>I would like to know how to write a specific macro that handles the
>following
[quoted text clipped - 8 lines]
> $ value, and have this run for the complete spreadsheet.
> Thanks in advance

Reply to this Message

Biochemist - 08 Dec 2007 18:13 GMT

I am sorry can you give me more information as I am new to using macro and
the recorder, thanks

> Turn the macro recorder on, do a Data>Text To Columns with a space
> delimiter, this will give you the code.
[quoted text clipped - 11 lines]
> > $ value, and have this run for the complete spreadsheet.
> > Thanks in advance

Reply to this Message

Bob Phillips - 08 Dec 2007 18:34 GMT

This might help more than anything I could offer
http://www.mrexcel.com/tip077.shtml

Signature

---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

>I am sorry can you give me more information as I am new to using macro and
> the recorder, thanks
[quoted text clipped - 16 lines]
>> > $ value, and have this run for the complete spreadsheet.
>> > Thanks in advance

Reply to this Message

Joel - 08 Dec 2007 20:53 GMT

Sub Gettext()

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const TxtDirectory = "C:\temp\"
Const ReadFileName = "test.txt"
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0

Set fsread = CreateObject("Scripting.FileSystemObject")
ReadPathName = TxtDirectory + ReadFileName
Set fread = fsread.GetFile(ReadPathName)
Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault)

RowCount = 1
Do While tsread.atendofstream = False

InputLine = Trim(tsread.Readline)

NewDate = Trim( _
Left(InputLine, InStr(InputLine, " ") - 1))
NewDate = Left(NewDate, 2) & "/" & Mid(NewDate, 3, 2) & _
"/" & Mid(NewDate, 5, 4)
NewDate = DateValue(NewDate)
InputLine = Trim( _
Mid(InputLine, InStr(InputLine, " ") + 1))
Vendor = Trim(Left(InputLine, InStr(InputLine, "$") - 1))
InputLine = Trim( _
Mid(InputLine, InStr(InputLine, "$") + 1))
Firstamount = Val(Trim( _
Left(InputLine, InStr(InputLine, "$") - 1)))
SecondAmount = Val(Trim( _
Mid(InputLine, InStr(InputLine, "$") + 1)))
Range("A" & RowCount) = NewDate
Range("B" & RowCount) = Vendor
Range("C" & RowCount) = Firstamount
Range("D" & RowCount) = SecondAmount

RowCount = RowCount + 1
Loop
tsread.Close

End Sub

> This might help more than anything I could offer
> http://www.mrexcel.com/tip077.shtml
[quoted text clipped - 19 lines]
> >> > $ value, and have this run for the complete spreadsheet.
> >> > Thanks in advance

Reply to this Message

Joel - 08 Dec 2007 21:06 GMT

Sub Format_Report()

With Sheets("Sheet1")
.Copy after:=Sheets(Sheets.Count)
ActiveSheet.Name = "NewSheet"
End With
With Sheets("NewSheet")
RowCount = 1
Do While .Range("C" & RowCount) <> ""
Data = .Range("C" & RowCount)
With Sheets("LookupSheet")
Set c = .Columns("D:D").Find(what:=Data, _
LookIn:=xlValues)
If Not c Is Nothing Then
Newdata = c.Offset(rowoffset:=0, columnoffset:=1)
Sheets("NewSheet").Range("C" & RowCount) = Newdata
End If
End With

RowCount = RowCount + 1
Loop
End With
End Sub

Reply to this Message