Consulting

Results 1 to 8 of 8

Thread: Problem running code on a different sheet

  1. #1
    Administrator
    Chat VP
    VBAX Guru johnske's Avatar
    Joined
    Jul 2004
    Location
    Townsville, Australia
    Posts
    2,872
    Location

    Problem running code on a different sheet

    Hi,

    Can anyone help me out with this problem? (done with Office2000) It does exactly what it's supposed to do if I run it on sheet1, or, on a COPY of sheet1 renamed sheet4, but when I go to sheet2 or 3 and try to run it I get an error message???? (perhaps it's just my old machine playing up again, will enclose a zip copy)...PS I know the variables haven't been declared, but that made no difference...

    TIA

    Sub ShowForm()
    '//Look in the A column for an empty row
          With ActiveSheet.Range("A:A")
                Set FromOrigin = .Find("", LookIn:=xlValues)
          End With
    '//Prompt for the number of columns required
          NumColumns = Application.InputBox(prompt:="How many columns? (Enter an integer to continue)", Title:="NUMBER OF COLUMNS", Type:=2)
          If NumColumns = Empty Then End
    '//Set the default number of rows = 4
          For Y = 0 To 3
                For X = 0 To NumColumns - 1
                      With ActiveSheet
                            FromOrigin.Offset(Y, X).Select
       '//read heading so can display it in input box
                            Heading = ActiveSheet.Range("A1").Offset(0, X)
                            DataIs = Application.InputBox(prompt:="Type in entry for " & Heading & " and hit Enter - leave blank or click Cancel to Exit", Title:=Heading, Type:=2)
                            If DataIs = Empty Then End
                            Selection.Value = DataIs
                      End With
                Next X
          Next Y
    End Sub

  2. #2
    VBAX Master TonyJollans's Avatar
    Joined
    May 2004
    Location
    Norfolk, England
    Posts
    2,291
    Location
    I had to remove references to EasyOffice DLLs but, after that, .

    The reason for not working is that column A is empty and the .Find returns Nothing rather than the A1 which I think you are expecting.

    Instead of looking for an empty string you could use ..

    Set FromOrigin = ActiveSheet.Range("A65535").End(xlUp)
    Enjoy,
    Tony

    ---------------------------------------------------------------
    Give a man a fish and he'll eat for a day.
    Teach him how to fish and he'll sit in a boat and drink beer all day.

    I'm (slowly) building my own site: www.WordArticles.com

  3. #3
    Administrator
    Chat VP VBAX Guru johnske's Avatar
    Joined
    Jul 2004
    Location
    Townsville, Australia
    Posts
    2,872
    Location
    Thanx SO much Tony,

    I dint know that one

    Set FromOrigin = ActiveSheet.Range("A65535").End(xlUp)
    , so I made the other up myself.

    But, I dint even remove the EasyOffice references (as these DLLs are on my machine) and just pasted your code and it worked immediately!

    I think this can be marked as solved now...

    Regards,
    John

  4. #4
    Administrator
    Chat VP VBAX Guru johnske's Avatar
    Joined
    Jul 2004
    Location
    Townsville, Australia
    Posts
    2,872
    Location
    Whoops, sorry, I spoke too soon.. ..on checking, there are still problems (need to find the first empty row to start from on sheet1 - and all the others - and it goes to row30 on sheet1) but I think you've given me what I needed to get it sorted...the logical flaw would seem to be that I need a header column << edit: (I mean row, not column)

    TA,
    John

  5. #5
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    John,

    See if this does what you want it to do. Note you need to add headings to sheet 2.

    Sub ShowForm() 
          '//Look in the A column for an empty row
          With ActiveSheet.Range("A1")
        Range("A1").Select
        Do
        If IsEmpty(ActiveCell) = False Then
            ActiveCell.Offset(1, 0).Select
        End If
        Loop Until IsEmpty(ActiveCell) = True
        Set FromOrigin = ActiveCell
        End With
          '//Prompt for the number of columns required
          NumColumns = Application.InputBox(prompt:="How many columns? (Enter an integer to continue)", Title:="NUMBER OF COLUMNS", Type:=2)
          If NumColumns = Empty Then End
    '//Set the default number of rows = 4
          For Y = 0 To 3
                For X = 0 To NumColumns - 1
                      With ActiveSheet
                            FromOrigin.Offset(Y, X).Select
                            Heading = ActiveSheet.Range("A1").Offset(0, X)
                            DataIs = Application.InputBox(prompt:="Type in " & Heading & " and hit Enter - leave blank or click Cancel to Exit", Title:=Heading, Type:=2)
                            If DataIs = Empty Then End
                            Selection.Value = DataIs
                      End With
                Next X
          Next Y
    End Sub
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  6. #6
    Administrator
    Chat VP VBAX Guru johnske's Avatar
    Joined
    Jul 2004
    Location
    Townsville, Australia
    Posts
    2,872
    Location
    Thanx for that one lucas (another for the old memory bank ) but once Tony pointed out my mistake of not having a header row I changed it to the following and it worx fine now


    Sub ShowForm()
    If ActiveSheet.Range("A1") = Empty Then GoTo ErrorMsg
    '//count number of data fields
    ActiveSheet.Range("A1").Select
    NumColumns = 1 
    Do While Selection.Value <> ""
          Selection.Offset(0, 1).Select
          NumColumns = NumColumns + 1
          Loop
    '//find empty row
    With ActiveSheet.Range("A:A")
    Set FromOrigin = .Find("", LookIn:=xlValues)
    End With
    '//enter data 
    For Y = 0 To 65000
    For X = 0 To NumColumns - 2
    With ActiveSheet
    FromOrigin.Offset(Y, X).Select
    Heading = ActiveSheet.Range("A1").Offset(0, X)
    DataIs = Application.InputBox(prompt:="Type in " & Heading & " and hit Enter - leave blank or click Cancel to Exit", Title:=Heading, Type:=2)
    If DataIs = Empty Then End
    Selection.Value = DataIs
    End With
    Next X
    Next Y
    Exit Sub
    ErrorMsg: MsgBox "You need headings/labels in the 1st row that describe all your data fields", vbOKOnly, "HEADINGS NEEDED FIRST..."
    End Sub

  7. #7
    Administrator
    Chat VP VBAX Guru johnske's Avatar
    Joined
    Jul 2004
    Location
    Townsville, Australia
    Posts
    2,872
    Location
    @ lucas. PS: Pls dont get me wrong, I'm not ungrateful for your contribution, it's just that for this specific problem - what I already have works....

    However for a more "general-purpose" type solution (that I'm working towards) your code - or a variation on it - would probably be more suitable and will most probably be needed. That's what I meant by the memory banks

    Thanx!
    John

  8. #8
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    John,

    Not to worry my friend, I was just throwing in my 2 cents. I actually liked the way your code behaved better. By the way, I'm not a real coder like some of the people here. I just like to tinker with the code and see if I can get it to do what I want it to do. Glad your fixed up.

    Ps would love to see it when you get it the way you want it, looks useful. You might consider adding it to the kb at some point...good day!
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •