97 vs 2000

[viper]

I have this workbook that works fine on my computer(using 2000) but I e-mailed the workbook to a guy (using 97) and he gets an error message on this line:
Cells(c.Row + 1, 4).Rows.EntireRow.Insert

Can anyone give me some reasons why? Or help in fixing it so that it will work properly.

Thanks,

[Mark O'Brien]

Viper,

What's the line of code that sets "c"?
This may be the true culprit, it looks like the problem stems from "c" not being set properly.

[NateO]

Right on Mark, if c is a range, the following fires in '97:

Sub inSrt()

Dim c As Range

Set c = ActiveCell

c.EntireRow.Insert

End Sub

[viper]

here is the code, I got it from TsTom to help add a row if a cell is empty.

Dim c
For Each c In Range("CodeRange")
ActiveSheet.Unprotect ("password")
Debug.Print c.Value
If c.Value = Val(TextBox1) Then
If Cells(c.Row, 4) <> "" Then
Cells(c.Row + 1, 4).Rows.EntireRow.Insert
Cells(c.Row + 1, 4) = TextBox4

thanks,

[NateO]

Assuming your data is in the the same column as CodeRange, try the following:

Sub test()

Dim c As Range

For Each c In Range("CodeRange")

ActiveSheet.Unprotect ("password")

Debug.Print c.Value

If c.Value = Val(TextBox1) Then

    If c.Value <> "" Then

        c.Offset(1, 0).EntireRow.Insert 'Change 0 is necessary (same column)

        c.Offset(1, 0).Value = TextBox4 'Change 0 is necessary (same column)

    End If

End If

Next c

ActiveSheet.Protect ("password")

End Sub

_________________
Cheers, NateO

[ This Message was edited by: NateO on 2002-05-19 00:12 ]

[viper]

Thanks Nate,

But it added a new row in column A and inserted the figure from the textbox.

What Tom's did was check column D, if it was blank then it passed his code and inserted the figure via my code, but if the cell in column D wasn't blank then it inserted a row below the cell and then inserted the figure into the new cell in column D.

Here is my complete code I am using.

If Not IsNumeric(TextBox1) Then
MsgBox "Need a Number"
TextBox1.SetFocus
Exit Sub
End If
Dim c
For Each c In Range("CodeRange")
ActiveSheet.Unprotect ("password")
Debug.Print c.Value
If c.Value = Val(TextBox1) Then
If Cells(c.Row, 4) <> "" Then
Cells(c.Row + 1, 4).Rows.EntireRow.Insert
Cells(c.Row + 1, 4) = TextBox4
Range("e9").Copy
Range("e10:e130").Select
Selection.PasteSpecial Paste:=xlFormulas

Range("D9:D130").Select
Selection.TextToColumns Destination:=Range("D9"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1)
Range("E9").Select
Else
Cells(c.Row, 4) = TextBox4
Range("D9:D130").Select
Selection.TextToColumns Destination:=Range("D9"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1)
Range("E9").Select
End If
Exit For
End If
Next
ActiveSheet.Protect ("password")

If TextBox1.Value < 1 And TextBox1.Value > 85 Then
MsgBox "Range 1 to 85"
TextBox1.SetFocus
Exit Sub
End If

Thanks,

[NateO]

Hello Viper, if CodeRange is in column A and you want to work with data in column D, change:

If c.Value <> "" Then

 c.Offset(1, 0).EntireRow.Insert 'Change 0 is necessary (same column)

 c.Offset(1, 0).Value = TextBox4 'Change 0 is necessary (same column)

End If



to



If c.Offset(, 3).Value <> "" Then

 c.Offset(1, 0).EntireRow.Insert 'Change 0 is necessary (same column)

 c.Offset(1, 3).Value = TextBox4 'Change 0 is necessary (same column)

End If

The second parameter of offset (3) lets Excel know that while we're using column A, we want to look at the data 3 columns right of our current position.

_________________
Cheers, NateO

[ This Message was edited by: NateO on 2002-05-19 00:35 ]

[viper]

The code above still makes no difference. The code still doesn't insert the new row at the correct location. The code I do use for this workbook works excellent but only with Excel 2000, I can't get it to work with 97. I'm at a loss as to what to do.

[NateO]

The code still doesn't insert the new row at the correct location.

Viper, inserting a row affects every column, you can't insert a row in the wrong column, just the wrong row.

So, this is proving to be interesting with the partial code listing. The code below is what I meant by changing the offset. Again, I'm assuming "CodeRange" is in column A, of course this will not work if that's not true, which column is "CodeRange" in?

Dim c As Range, d As Range

Set d = [d9:d130]

ActiveSheet.Unprotect ("password")

For Each c In Range("CodeRange") 'Is CodeRange in Column A?

    Debug.Print c.Value

    If c.Value = Val(TextBox1) Then

        If c.Offset(, 3).Value <> "" Then

            c.Offset(1, 0).EntireRow.Insert

            c.Offset(1, 3).Value = TextBox4

            Range("e9").Copy

            Range("e10:e130").PasteSpecial Paste:=xlFormulas

            d.TextToColumns Destination:=Range("D9"), DataType:=xlDelimited, _

             TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _

             Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _

             :=Array(1, 1)

        Else: c.Offset(0, 3).Value = TextBox4

            d.TextToColumns Destination:=Range("D9"), DataType:=xlDelimited, _

             TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _

             Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _

             :=Array(1, 1)

        End If

        Exit For

    End If

Next

I used the offset for row insertion yesterday on a program for my buddy (in xl '97) and it worked well, the only reason I'm mentioning it.

_________________
Cheers, NateO

[ This Message was edited by: nateo on 2002-05-20 19:16 ]

[viper]

Nate,

I appreciate all you're doing for this, I just pasted your code into the workbook and still not working. What it does now is when i enter the data and click okay it gives me the error "no data to parse". so it didn't copy textbox4 to the cell. I have pasted my data into this so I hope it looks okay. But if you look Column A is the "CodeRange" which is the numbers that textbox1 will contain. Column b is for discription only and C is for fixed data, Column D is where the data from Textbox4 will be placed if textbox1 matches the code from Column A So if D9 is not blank and the user inputs a 1 into textbox1 then when okay is pressed the code should insert a new row because D9 is already occupied.

Sorry just couldn't get it to look good. Couldn't paste it need more practice.



_________________
Soon I hope to be answering alot of these questions instead of asking them.

viper

[ This Message was edited by: viper on 2002-05-20 19:38 ]

[ This Message was edited by: viper on 2002-05-20 19:39 ]

[ This Message was edited by: viper on 2002-05-20 19:41 ]

[ This Message was edited by: viper on 2002-05-20 19:45 ]