Posts by cbanks

Re: Setting Column As Integer

not working for what i need. getting the duplicate declaration in scope for

lrow = lrow+1

Hi, I have written a macro to record some information to cells on a spreadsheet. Below is my code. As you can see I already have row setup in the code, but I now want to add column as an integer. So that if something were recorded in col m, then it would add 1 to colu and the next information would be added in col n.

Col = 13 (M) to start with. Thanks for your help.

Dim row As Integer
    Dim colu As Integer


If Trim(txt(8, 10, 25)) = Workbooks("Book1").Worksheets(1).Range("K" & row) Then
        MyScreen.Area(8, 4, 8, 5).Copy
        MyScreen.Area(22, 40, 22, 41).Paste
        SendKey "<ENTER>"
            If MyScreen.Area(7, 57, 7, 57) = "A" Then
            Workbooks("Book1").Worksheets(1).Range(colu & row) = Trim(txt(7, 57, 74))'getting my error here.. something wrong with colu
            colu = colu + 1
            End If

Re: Read Only File

ActiveWorkbook.SaveAs Filename:= _
        "C:\Documents and Settings\Owner\My Documents\Book1.xls", FileFormat:= _
        xlNormal, Password:="Passwordgoeshere", WriteResPassword:="orhere", ReadOnlyRecommended:=True, _
        CreateBackup:=False

Re: Msgbox Macro For Automated Vlook-up

added it to the run macro button.

Re: Continue Finding

The code below will continue until N is blank. It appears as if you are starting your seach with N3. Therefore row begins with 3. If you begin with another row then you can change it.

Sub Find_cust()
Dim row As String
row = 3
Do Until Worksheets(1).Range("N" & row) = ""
    If Range("N3").Value = "" Then
        MsgBox "Please enter a customer name"
        Exit Sub
         
    End If
     
    valueToLookFor = Range("N3").Value
    Set found = Worksheets("CD").Range("b:b").Find(valueToLookFor, LookIn:=xlValues)
    If Not found Is Nothing Then
        iRow = found.row
        Cells(iRow, "A").Select
         
    End If
     row = row + 1
        DoEvents
    Loop
   
End Sub

Re: Msgbox Macro For Automated Vlook-up

Here ya go.. Columns entered need to be in this format A:A, B:B, A:B. This is just a start. It will work, but could be made more efficient and user-friendly.

Re: Msgbox Macro For Automated Vlook-up

I cant get the file small enough

Re: Msgbox Macro For Automated Vlook-up

its possible. can you post the vlookup formula, and anything else you may have already started. this will require a userform, and many different options. if anyone else is working on this please post to save me the time.

Re: Amortization Table Formula

Try copy>paste special> values. This should do it. As before I cannot get the attachment to open. Just trying to help

Re: Save Work Book In 2 Separate Directories

I removed the first save as I did not see a reason for it. If needed you may want to re-add.

Sub SAVEANDEXITTHESLATE()
     
    Dim date1
    Dim date2
    Dim xlCalc As XlCalculation
    Dim Save1 As String
    Dim Save2 As String
    Save1 = "c:\Documents and Settings\me\Desktop\"
    Save2 = "H:\"
    
    xlCalc = Application.Calculation
    Application.Calculation = xlCalculationManual
    On Error GoTo CalcBack
    Application.ScreenUpdating = False
    date1 = Now()
    date2 = Format(date1, "mmm d yyyy hh mm")
    ChDir Save1
    ActiveWorkbook.SaveAs Filename:= _
    Save1 & "test " + date2 + ".xls" _
     
    ChDir Save2
    ActiveWorkbook.SaveAs Filename:= _
    Save2 & "test " + date2 + ".xls"
    ActiveWorkbook.Close
    Application.Calculation = xlCalc
    Exit Sub
CalcBack:
    Application.Calculation = xlCalc
    Application.ScreenUpdating = True
End Sub

Re: Copy Formula With Fill Handle

Kenneth is advising you to fill in cell B4 and B5 with the desired result:

B4 "=A4"
B5 "=A25"

and then autofill. Excel needs to see a trend to mimick one.

Re: Amortization Table Formula

I have tried, but I cannot get your attachment to open. Have you tried print merging to create your letters? As I have not seen your attachment I can only suggest an answer from what I am reading.

Re: Paste 1004 From Recorded Code

Application.CutCopyMode = True

Re: Macro In Personal.xls Runs On The Personal.xls Workbook Itself

No problem. I go through the same thing here everyday. Have you tried working with active workbook? That seems to work well for me.

Re: Userform Disappears After Clicking No

please post example code, or example file. Thanks

Re: Reset Multiple Dropdown Boxes

Try this post it may help

http://www.ozgrid.com/forum/showthread.php?t=23329

Re: Formulas Not Reading When Auto Filled

Try going into tools<options<calculation and turning it to Automatic. You probably have this turned off. If all else fails press F9 to calculate after you autofill.

Re: Macro In Personal.xls Runs On The Personal.xls Workbook Itself

To improve your code, and functionality try naming your workbooks. Often you will run into problems using macros with undefined workbook names. In your code you are referring to workbook (2). Why not refer to the exact workbook you are working with (i.e. MVR Totals.xls). This would require you saving a sheet labeled MVR Totals to use as a template. I do not understand what you are trying to accomplish with the sheets add, but i left it in there. Best of luck. Chris.

Sub MVRTotals()
     
    Workbooks("MVR Totals.xls").Worksheets("Sheet1").Select
     
    Dim visitcomp As Integer
    Dim indicator As Integer
     
    Row = 2
    indicator = 1
     
    Do Until Workbooks("MVR Totals.xls").Worksheets("Sheet1").Range("A" & Row) = ""
        indicator = 0
        If Workbooks("MVR Totals.xls").Worksheets("Sheet1").Range("AB" & Row) > 1 Then
            Worksheets("Sheet1").Rows(Row).Delete
            indicator = 1
        End If
        If indicator = 0 Then
            Row = Row + 1
        End If
         
        Workbooks("MVR Totals.xls").Worksheets("Sheet1").Select
        Sheets.Add
         
    Loop
     
       Workbooks("MVR Totals.xls").Worksheets("Sheet1").Select
    Sheets.Add
     
End Sub

Here is how you would use your code with workbook (2).

Sub MVRTotals()
     
    Workbooks(2).Worksheets("Sheet1").Select
     
    Dim visitcomp As Integer
    Dim indicator As Integer
     
    Row = 2
    indicator = 1
     
    Do Until Workbooks(2).Worksheets("Sheet1").Range("A" & Row) = ""
        indicator = 0
        If Workbooks(2).Worksheets("Sheet1").Range("AB" & Row) > 1 Then
            Worksheets("Sheet1").Rows(Row).Delete
            indicator = 1
        End If
        If indicator = 0 Then
            Row = Row + 1
        End If
         
        Workbooks(2).Worksheets("Sheet1").Select
        Sheets.Add
         
    Loop
     
       Workbooks(2).Worksheets("Sheet1").Select
    Sheets.Add
     
End Sub

Re: Pasting Freezing Up Computer

getting an object error. I will move the code into the userform. Im not sure why i did not do that to begin with but I will now.

on my old code when i stopped trying to clear everything worked fine.

Re: Pasting Freezing Up Computer

User form is there to select which workbook, and what they want to be put in for there name in column R. There are 5 different options for the workbooks. It could be 03-26-2007 A, 03-26-2007 B, etc...

How do i clear the info without selection the column?