Macro to format data.

[diaare]

I have a report that needs to be imported into excel from a text file and
formatted daily. I would like to automate the formatting process with a
macro, unfortunately, my VBA skills are not very good.

Could one of you please help me with the macro?

I think what I need it to do should be fairly simple.

If the cell in column e is blank then I want cells a, b, c, and d
(respectively) to all move one cell to the right. This should be be applied
to the entire set of data (usually about 350 rows)

Thats it.

Thanks in advance,
DIane

 

[Bob Phillips]

Sub ProcessAllLines()
Dim LastRow As Long
Dim i As Long

With Activesheet

LastRow = .Cells(.Rows.Count,"A").End(xlUp).Row
For i = 1 To LastRow

If .Cells(i, "E").Value = "" Then

.Cells(i, "A").Resize(,4).Cut .Cells(i, "B")
End If
Next i
End With

--
---
HTH

Bob


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

 

[Dave Peterson]

Start a new workbook

Start recording a macro that will be stored in this macro workbook.

File|Open your text file that needs to be imported. Format it the way you
like. Insert columns, add headers, freeze panes, widen columns, add filters, do
the page setup--everything you can think of.

When you're done, save that workbook with the macro.

I like to put a big button from the Forms toolbar on the only worksheet in that
macro workbook and assign this macro to that big button. I'll add a few
instructions to that sheet, too.

If the file name to open is always the same and in the same location, then I'm
about done. If the location or file name changes, I'll tweak the code to ask
for the file.

My tweaked code could look a little like:

Option Explicit
Sub Testme01()

Dim myFileName As Variant

myFileName = Application.GetOpenFilename(filefilter:="Text Files, *.Txt", _
Title:="Pick a File")

If myFileName = False Then
MsgBox "Ok, try later" 'user hit cancel
Exit Sub
End If

Workbooks.OpenText Filename:=myFileName '....rest of recorded code here!

End Sub

 

[Joel]

RowCount = 1
Do While Range("A" & RowCount) <> ""
If Range("E" & RowCount) = "" Then
Range("A" & RowCount).Insert Shift:=xlToRight
End If
RowCount = RowCount + 1
Loop

 

[diaare]

Perfect!

Thanks all of you for your help. Eventually I am going to get around to
learning VBA for myself...thank goodness you all are out here to help until
then.

Diane