Posts by Batanen

Duh! Okay....this is what I get writing too many 'quick' files. So, it should have been:

Dim a As Long
Dim b As Long
Dim c As Long
Dim d As Long

Right?

Thanks Miranda!

I keep getting an Overflow error using the following code:

Dim a As Integer
 Dim b As Integer
 Dim c As Integer
 Dim d As Integer
 
 a = 3
 b = 3
 c = 3
 d = 3
 
Application.ScreenUpdating = False
 
 Do
    If ActiveSheet.Cells(a, "C") = "2" Then
    Range(Cells(a, "A"), Cells(a, "E")).Select
    selection.Copy
    Cells(b, "G").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range(Cells(a, "A"), Cells(a, "E")).Select
    selection.Delete Shift:=xlUp
    b = b + 1
    ElseIf ActiveSheet.Cells(a, "C") = "4" Then
    Range(Cells(a, "A"), Cells(a, "E")).Select
    selection.Copy
    Cells(c, "M").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range(Cells(a, "A"), Cells(a, "E")).Select
    selection.Delete Shift:=xlUp
    c = c + 1
    ElseIf ActiveSheet.Cells(a, "C") = "3" Then
    Range(Cells(a, "A"), Cells(a, "E")).Select
    selection.Copy
    Cells(d, "S").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range(Cells(a, "A"), Cells(a, "E")).Select
    selection.Delete Shift:=xlUp
    d = d + 1
    Else: a = a + 1
    End If
 Loop Until ("A" & a) = "***EOF"
     
MsgBox ("Completed")

This overflow error occurs on line 22031. Does anyone else have this problem? Is there a way round it?

Thanks!

Well, here's what I figured out. For some reason, in Excel VBA, it's no longer reading the 'selection' line properly. I know this is not a problem with Win XP Service Pack 2. I have, however, figured a work-around. main reason I'm posting this is just in case anyone else has this error.

What I've had to do is change code from:

Rows(d).Select 
selection.Delete 'Shift:=xlUp

to:

Rows(d).Select 
Rows(d).Delete 'Shift:=xlUp

and code:

Rows("1:2").Select 
With selection 
    .HorizontalAlignment = xlCenter 
    .VerticalAlignment = xlBottom 
    .WrapText = False 
    .Orientation = 0 
    .AddIndent = False 
    .ShrinkToFit = False 
    .MergeCells = False 
End With

to:

Rows("1:2").Select 
With Rows("1:2")
    .HorizontalAlignment = xlCenter 
    .VerticalAlignment = xlBottom 
    .WrapText = False 
    .Orientation = 0 
    .AddIndent = False 
    .ShrinkToFit = False 
    .MergeCells = False 
End With

In a nutshell, changing 'selection' to what I have that has actually being selected (rows in this case).

Thanks for the help earlier Bnix!

Shane

"d" is a variable set as an integer in the Dim statements. For some reason, this is happening with ALL my macros. With the above 'With selection' code, the message box will give me an error stating: "Compile error: Expected Function or variable." The only thing puzzling me is the fact that I have been using this macro for quite some time now, and I have not altered it. But this morning, it just started doing this.

Come to think of it, this actually started around last Wednesday....I was creating a macro and started getting this error....I've tried repairing and reinstalling office to see if that helped, but it did not:(

I've got a code I've been using for several months now, and all has been fine with it until recently. The main problem is, part of the code

Rows(d).Select
            selection.Delete 'Shift:=xlUp

will now halt the macro and give me an error.

Has anyone else had this problem?

I've found a partial work-around, which is to chance the above code to

Rows(d).selection.Delete 'Shift:=xlUp

which can get me by if I have to, but then I have code that will bring it to a halt like

Rows("1:2").Select
    With selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With

Does anyone have any ideas on how to work around this or why in the world my macro no longer works?????

Thanks!
Shane

OH! Okay...I see how it's done now. Thanks! I'll give it at try

Okay...I've written some code that works fine and does what I need it to. However, I am trying to expand on it as I will be using it quite often. I have it posted below ^praying to the computer gods that I used the 'Code' tag right^

Currently, this will find different account numbers in column "C" and place two (2) blank lines between the accounts. I would like to add code that will sum columns "G" and "H" for each account. I am assuming that I'm going to have to use a 'Dim ? As Range' statement, but I cannot figure out how to do it since each account has a different number of rows.

Any suggestions? They will be greatly appreciated.

Thanks in advance!

Sub insert()
'
' insert Macro
'


'
Dim a As Integer


a = 4


Do
    If ActiveSheet.Cells(a, "C") = ActiveSheet.Cells(a - 1, "C") Then
        a = a + 1
    Else:   Rows(a).Select
            selection.insert Shift:=xlDown
            Rows(a + 1).Select
            selection.insert Shift:=xlDown
            R = Range(a, "G").Select
            a = a + 3
    End If
Loop Until ActiveSheet.Cells(a, "A").Value = "***EOF"




End Sub

Thanks! For some reason, when I created it using the macro record, it did not record the Name:="5160"

Thanks for the help! Will keep the template suggestion in mind!

I have created several VBA Word programs to create mailing labels. I am stuck at the moment. The programs work fine for the most part, however, Word keeps defaulting the labels to the last ones used (usually file folder labels).

Does anyone know if any code that will select the correct address label in VBA?

Any help is greatly appreciated!

Shane

Thank you ever so much. This is really helping me ALOT! I'll be posting back if I have any other questions about this project (I'm sure I will......)

Actually, I think I *may* have found some code that can help me...but I still need help with the code. I want to run the VBA Macro out of Word, since the document title changes from day to day...but the Excel Spreadsheet name will stay the same. So, with the code below, how would I call a spreadsheet (Daily.xls) instead of opening a new spreadsheet? Also, if anyone knows, will this automatically open Excel, or should it be open to begin with?

Thanks!

Shane

Code:

Sub CallExcel()

' Declare an object variable to hold the object reference.

Dim ExcelSheet As Object
Set ExcelSheet = CreateObject("Excel.Sheet")

'This code starts the application creating the object, in this case, a
'Microsoft Excel spreadsheet. Once an object is created, you reference it in
'code using the object variable you defined. In the following example, you
'access properties and methods of the new object using the object variable,
'ExcelSheet, and other Microsoft Excel objects, including the Application
'object and the Cells collection.

' Make Excel visible through the Application object.
ExcelSheet.Application.Visible = True
' Place some text in the first cell of the sheet.
ExcelSheet.Application.Cells(1, 1).Value = "This is column A, row 1"
' Save the sheet to C:\test.xls directory.
ExcelSheet.ActiveWindow.WindowState = xlMaximized
ExcelSheet.SaveAs "C:\TEST.XLS"
' Close Excel with the Quit method on the Application object.
'ExcelSheet.Application.Quit
' Release the object variable.
Set ExcelSheet = Nothing

End Sub

Thanks! I'd appreciate it

I have two 'macros' that work with each other to pull information I need daily. One is in excel that will go to the first availble cell in the worksheet and paste the information. The other is in Word that will copy the information. Both will perform a certain task, then a message box will appear that is basically a 'pause' until I work on the next step in the other macro. Here's how it runs:

Excel:
Finds first empty cell in column 'b' of sheet one, then message box comes up.

Word:
Run the macro to find the information needed then copies to clipboard, then message box pauses.

Excel:
Pastes infromation from the clipboard, goes to sheet 2 to the first empty cell of column "B" and message box pauses

This is a lot of back-and-forth that I have to do (when my system doesn't lock up on me...). Is there any way that either I can combine all this into one VBA macro so I can just run it and be done with it...or is there any way to copy all the information I need from Word into the clipboard? In the past, I have been able to copy several items to the clipboard and a box would come up with showing I had these items in the clipboard, but I have no idea how it happend.

Any and all suggestions will be greatly appreciated!

Thank you so much! The book I'm learning from, Excel VBA for the Absolute Beginner, doesn't even mention Pivot Tables. I never even heard of them before yesterday. From what I read, I might start using them quite a bit!

Thanks again!
Shane

Okay. I just found http://www.ozgrid.com/Excel/excel-pivot-tables.htm and I have skimmed through it and I am starting to read it now. Out of curiousity, would it be possible to have the region name in a drop-down box so our sales reps can choose which region they want to view the totals for? If so, I think it would be one of the easiest routes to go. (not to mention, probably make it a smaller file rather than having 30+ worksheets):cool:

considering the time restraints I currently have, I think the pivot table approach may work. Two questions:1) how do I create a pivot table and 2) can it be programmed as a macro?

I have a worksheet that I run every week. The main data I'm working with are four cells (region, type, price and quantity). I am trying to divide this into new sheets for the different regions(already have the macro creating and naming them) then display the average of the price and quantity for each type in the region.

If there are any questions, feel free to ask. I'm going to use the HTML maker add-in to *hopefully* clarify.

Thank you SO much! Worked like a charm :cool:

I am working on an import file project. I keep getting an error with this part though. It would probably be easier to just program in the rows that will contain the imported page headers, but it varies each time.

I know Cell "H1" will always be "Prod", which is why I start this part as searching from "A3" If anyone can help with this code, or knows an easier way to do this, please let me know!

Thanks in advance!
Shane

Code:

' Take Page Headers out of document

Range("A3").Select

Cells.Find(What:="Prod", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
True).Activate

Do

Selection.EntireRow.Delete

Selection.EntireRow.Delete

Cells.Find(What:="Prod", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
True).Activate

Loop Until ActiveCell = ("H1")