This clever code ain't doing nothin, why?

[Jim08]

I have a spreadsheet that has the account number in the first collum of the
parent row followed by some children rows that have no account number. I want
to load the spreadsheet into an access database and normalize it but I need
to load the account number into the children rows. So I tried the code below,
it however does nothing. Any help is apprecaited
Thanks
Jim
'
' Plug account number into mailbox records
'
Public Sub plugAccountNumber()

Dim accountNumber As Long
Dim startRow As Long
Dim endRow As Long
Dim rowNdx As Long

Application.ScreenUpdating = False
On Error GoTo EndMacro:

' Just doing 50 as a test

startRow = 1
endRow = 50

For rowNdx = startRow To endRow

If Me.Cells(rowNdx, 1).Value <> "" Then
accountNumber = Me.Cells(rowIndex, 1).Value
Else
Me.Cells(rowIndex, 1).Value = accountNumber
End If

Next rowNdx

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True

End Sub

 

[Tim]

I could be wrong, but: -

(1) you don't need 'Me.' in the code
(2) you haven't defined what 'RowIndex' is (is it supposed to be 'rowNdx')
(3) you haven't defined what 'accountNumber' is

after fixing (1) and replacing RowIndex with rowNdx, it sort of worked, by
entering 0 in the first 50 rows of column A (which i guess is what Excel is
presuming the value of accountNumber is)!

hope that helps?!

Tim

 

[Mike H]

Hi,

A couple of points it wasn't running because of an incorrect variable

accountNumber = Me.Cells(rowIndex, 1).Value

should have been

accountNumber = Cells(rowNdx, 1).Value

You weren't seeing the error because of the on error line.

With that corrected what the macro now does is find a value and set account
number to that value. If it encounters a blank cell it populates that with
the account number it got from the previous pass through the loop.

My guess is it's doing what your telling it to do and not what you want.
Corrected code below.

Public Sub plugAccountNumber()

Dim accountNumber As Long
Dim startRow As Long
Dim endRow As Long
Dim rowNdx As Long
Application.ScreenUpdating = False
On Error GoTo EndMacro:
' Just doing 50 as a test

startRow = 1
endRow = 50

For rowNdx = startRow To endRow
If Cells(rowNdx, 1).Value <> "" Then
accountNumber = Cells(rowNdx, 1).Value
Else
Cells(rowNdx, 1).Value = accountNumber
End If

Next rowNdx

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True

End Sub


Mike

 

[Dave Peterson]

If the procedure is in the worksheet module, then the Me keyword is fine. The
problem is that you used both RowIndex and RowNdx (instead of just one of them).

And if your account numbers aren't Longs, you may have other trouble.

The modified version of your code worked fine for me--as long as it was placed
in the worksheet module:

Option Explicit
Public Sub plugAccountNumber()

Dim accountNumber As Variant
Dim startRow As Long
Dim endRow As Long
Dim rowNdx As Long

Application.ScreenUpdating = False
On Error GoTo EndMacro:

' Just doing 50 as a test

startRow = 1
endRow = 50

For rowNdx = startRow To endRow

If Me.Cells(rowNdx, 1).Value <> "" Then
accountNumber = Me.Cells(rowNdx, 1).Value
Else
Me.Cells(rowNdx, 1).Value = accountNumber
End If

Next rowNdx

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True

End Sub
Private Sub CommandButton1_Click()
Call plugAccountNumber
End Sub

=======
ps.

Debra Dalgleish has some other code that you may want to review:
http://contextures.com/xlDataEntry02.html#FillProg