Problem running code on a different sheet

[johnske]

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


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

[TonyJollans]

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)

[johnske]

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

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

Regards,
John :bink:

[johnske]

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

[lucas]

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

[johnske]

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:



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

[johnske]

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

[lucas]

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!