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... :bink:

TIA

Code:

---

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

---

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 ..

Code:

---

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

---

Thanx SO much Tony,

I dint know that one

Code:

---

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! :yes

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

Regards,
John :bink:

Whoops, sorry, I spoke too soon..:blush ..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) :bink:

TA,
John

John,

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

Code:

---

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

---

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 :bink:

Code:

---

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

---

@ 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 :bink:

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!