Autofill Problem

G

Guest

Hello!

I have a spreadsheet with three columns (A,B,C) with the following sample
data. I want to modify my existing macro to autofill the formulas in the
cells to the last row (for columns A and B). For some odd reason, the cells
autofill except for the last 5 cells in the column of 8K+ rows. Then it also
becomes a problem with Column B, which should update the cell to '000' when
Column A (Dept) changes: The row would then look like "004", "000", "000".
Here's the data

A B C
004 900 900
004 900 900
004 900 900
004 900 900
004 900 900
004 999 000
004 999 000
004 999 000
004 999 000
004 999 999
004 999 999
004 999 999
004 999 999
006 999 000 Column B should be "000"
006 999 000 Column B should be "000"
006 999 000 Column B should be "000"
006 999 000 " "
006 999 000 " "
006 999 000 " "


Here's the code. Any suggestions would be very HELPFUL!!!

Sub FillColumns()

Dim wks As Worksheet
Dim rng As Range
Dim LastRowInCol As Long
Dim LastRowToUse As Long
Dim myCol As Range
Dim RngToFix As Range


Set wks = ActiveSheet
With wks
Set RngToFix = .Range("a:b") 'try to reset the lastcell
LastRowInCol = .Cells.SpecialCells(xlCellTypeLastCell).Row
Set rng = Nothing
On Error Resume Next
Set rng = .Range(.Cells(2, "A"), .Cells(LastRowToUse, "B")) _
.Cells.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0


RngToFix.Replace What:="000", Replacement:="", _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
MatchCase:=False


LastRowToUse = 0
For Each myCol In RngToFix.Columns
LastRowInCol = .Cells(.Rows.Count, myCol.Column).End(xlUp).Row
If LastRowInCol > LastRowToUse Then
LastRowToUse = LastRowInCol
End If
Next myCol


Set RngToFix = RngToFix.Resize(LastRowToUse - 1).Offset(2, 0)
Set rng = Nothing
On Error Resume Next
Set rng = RngToFix.Cells.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If rng Is Nothing Then
MsgBox "No blanks found"
Exit Sub
Else
rng.FormulaR1C1 = "=R[-1]C"



End If

End With

End Sub


Any assistance would be appreciated!!!
 
G

Guest

Lizzy: I think the code is doing exactly what you asked it to do. the
problem has to do with the formula you inserted :rng.FormulaR1C1 = "=R[-1]C".
This formula is putting the data in the row above into the present cells.
Cell B14 has the formula =B13. b13 contains 999 so B14 gets 999.
 
D

Don Guillett

Please be a bit clearer about what you have and what you want. IF?? you have
a formula in a2 and a formula in b2 and a formula in c2 and you want to fill
down to what row? Is col A already filled in and you want to fill b & c, or
what.??

0 0 0
0
0
0
0
0
0
0


Sub fillitup()
lr = Cells(Rows.Count, "a").End(xlUp).Row
Range("a2:c2").AutoFill Range("a2:c" & lr)
End Sub
 
G

Guest

Sorry for the confusion...

Column C2 is already filled in correctly. It's column A & B that are giving
me a headache! It's not that I need the column to fill to a specific row;
however, In Column A, I need it to fill unti it reaches a new Department
Code, which it is doign just fine except for it stops when it gets just five
or six rows from the last row of data. Column B (SubDept) needs to fill down
with the subdepartment UNTIL it reaches a new DEPT (Column A). Column B
(SubDept) would then be '000' if Column A changes from A100 to B100. Then
the columns would look something like below.

Again, the autofill will not stop a specific row, but rather look for the
data to change codes...


004 000 000
004 100 100
004 100 100
006 000 000
006 100 100




Don Guillett said:
Please be a bit clearer about what you have and what you want. IF?? you have
a formula in a2 and a formula in b2 and a formula in c2 and you want to fill
down to what row? Is col A already filled in and you want to fill b & c, or
what.??

0 0 0
0
0
0
0
0
0
0


Sub fillitup()
lr = Cells(Rows.Count, "a").End(xlUp).Row
Range("a2:c2").AutoFill Range("a2:c" & lr)
End Sub

--
Don Guillett
SalesAid Software
(e-mail address removed)
Lizzy said:
Hello!

I have a spreadsheet with three columns (A,B,C) with the following sample
data. I want to modify my existing macro to autofill the formulas in the
cells to the last row (for columns A and B). For some odd reason, the
cells
autofill except for the last 5 cells in the column of 8K+ rows. Then it
also
becomes a problem with Column B, which should update the cell to '000'
when
Column A (Dept) changes: The row would then look like "004", "000",
"000".
Here's the data

A B C
004 900 900
004 900 900
004 900 900
004 900 900
004 900 900
004 999 000
004 999 000
004 999 000
004 999 000
004 999 999
004 999 999
004 999 999
004 999 999
006 999 000 Column B should be "000"
006 999 000 Column B should be "000"
006 999 000 Column B should be "000"
006 999 000 " "
006 999 000 " "
006 999 000 " "


Here's the code. Any suggestions would be very HELPFUL!!!

Sub FillColumns()

Dim wks As Worksheet
Dim rng As Range
Dim LastRowInCol As Long
Dim LastRowToUse As Long
Dim myCol As Range
Dim RngToFix As Range


Set wks = ActiveSheet
With wks
Set RngToFix = .Range("a:b") 'try to reset the lastcell
LastRowInCol = .Cells.SpecialCells(xlCellTypeLastCell).Row
Set rng = Nothing
On Error Resume Next
Set rng = .Range(.Cells(2, "A"), .Cells(LastRowToUse, "B")) _
.Cells.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0


RngToFix.Replace What:="000", Replacement:="", _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
MatchCase:=False


LastRowToUse = 0
For Each myCol In RngToFix.Columns
LastRowInCol = .Cells(.Rows.Count, myCol.Column).End(xlUp).Row
If LastRowInCol > LastRowToUse Then
LastRowToUse = LastRowInCol
End If
Next myCol


Set RngToFix = RngToFix.Resize(LastRowToUse - 1).Offset(2, 0)
Set rng = Nothing
On Error Resume Next
Set rng = RngToFix.Cells.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If rng Is Nothing Then
MsgBox "No blanks found"
Exit Sub
Else
rng.FormulaR1C1 = "=R[-1]C"



End If

End With

End Sub


Any assistance would be appreciated!!!
Click to expand...
Click to expand...
 
G

Guest

I guess all I really need to do is say: If Column C (Class) equals '000'
then update Column B (SubDept) to '000' . This should solve the problem,
right???

Don Guillett said:
Please be a bit clearer about what you have and what you want. IF?? you have
a formula in a2 and a formula in b2 and a formula in c2 and you want to fill
down to what row? Is col A already filled in and you want to fill b & c, or
what.??

0 0 0
0
0
0
0
0
0
0


Sub fillitup()
lr = Cells(Rows.Count, "a").End(xlUp).Row
Range("a2:c2").AutoFill Range("a2:c" & lr)
End Sub

--
Don Guillett
SalesAid Software
(e-mail address removed)
Lizzy said:
Hello!

I have a spreadsheet with three columns (A,B,C) with the following sample
data. I want to modify my existing macro to autofill the formulas in the
cells to the last row (for columns A and B). For some odd reason, the
cells
autofill except for the last 5 cells in the column of 8K+ rows. Then it
also
becomes a problem with Column B, which should update the cell to '000'
when
Column A (Dept) changes: The row would then look like "004", "000",
"000".
Here's the data

A B C
004 900 900
004 900 900
004 900 900
004 900 900
004 900 900
004 999 000
004 999 000
004 999 000
004 999 000
004 999 999
004 999 999
004 999 999
004 999 999
006 999 000 Column B should be "000"
006 999 000 Column B should be "000"
006 999 000 Column B should be "000"
006 999 000 " "
006 999 000 " "
006 999 000 " "


Here's the code. Any suggestions would be very HELPFUL!!!

Sub FillColumns()

Dim wks As Worksheet
Dim rng As Range
Dim LastRowInCol As Long
Dim LastRowToUse As Long
Dim myCol As Range
Dim RngToFix As Range


Set wks = ActiveSheet
With wks
Set RngToFix = .Range("a:b") 'try to reset the lastcell
LastRowInCol = .Cells.SpecialCells(xlCellTypeLastCell).Row
Set rng = Nothing
On Error Resume Next
Set rng = .Range(.Cells(2, "A"), .Cells(LastRowToUse, "B")) _
.Cells.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0


RngToFix.Replace What:="000", Replacement:="", _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
MatchCase:=False


LastRowToUse = 0
For Each myCol In RngToFix.Columns
LastRowInCol = .Cells(.Rows.Count, myCol.Column).End(xlUp).Row
If LastRowInCol > LastRowToUse Then
LastRowToUse = LastRowInCol
End If
Next myCol


Set RngToFix = RngToFix.Resize(LastRowToUse - 1).Offset(2, 0)
Set rng = Nothing
On Error Resume Next
Set rng = RngToFix.Cells.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If rng Is Nothing Then
MsgBox "No blanks found"
Exit Sub
Else
rng.FormulaR1C1 = "=R[-1]C"



End If

End With

End Sub


Any assistance would be appreciated!!!
Click to expand...
Click to expand...
 
D

Don Guillett

I guess I just getting old cuz I still don't understand. Feel free to send
me a workbook with before and after and your desires.

--
Don Guillett
SalesAid Software
(e-mail address removed)
Lizzy said:
I guess all I really need to do is say: If Column C (Class) equals '000'
then update Column B (SubDept) to '000' . This should solve the problem,
right???

Don Guillett said:
Please be a bit clearer about what you have and what you want. IF?? you
have
a formula in a2 and a formula in b2 and a formula in c2 and you want to
fill
down to what row? Is col A already filled in and you want to fill b & c,
or
what.??

0 0 0
0
0
0
0
0
0
0


Sub fillitup()
lr = Cells(Rows.Count, "a").End(xlUp).Row
Range("a2:c2").AutoFill Range("a2:c" & lr)
End Sub

--
Don Guillett
SalesAid Software
(e-mail address removed)
Lizzy said:
Hello!

I have a spreadsheet with three columns (A,B,C) with the following
sample
data. I want to modify my existing macro to autofill the formulas in
the
cells to the last row (for columns A and B). For some odd reason, the
cells
autofill except for the last 5 cells in the column of 8K+ rows. Then
it
also
becomes a problem with Column B, which should update the cell to '000'
when
Column A (Dept) changes: The row would then look like "004", "000",
"000".
Here's the data

A B C
004 900 900
004 900 900
004 900 900
004 900 900
004 900 900
004 999 000
004 999 000
004 999 000
004 999 000
004 999 999
004 999 999
004 999 999
004 999 999
006 999 000 Column B should be "000"
006 999 000 Column B should be "000"
006 999 000 Column B should be "000"
006 999 000 " "
006 999 000 " "
006 999 000 " "


Here's the code. Any suggestions would be very HELPFUL!!!

Sub FillColumns()

Dim wks As Worksheet
Dim rng As Range
Dim LastRowInCol As Long
Dim LastRowToUse As Long
Dim myCol As Range
Dim RngToFix As Range


Set wks = ActiveSheet
With wks
Set RngToFix = .Range("a:b") 'try to reset the lastcell
LastRowInCol = .Cells.SpecialCells(xlCellTypeLastCell).Row
Set rng = Nothing
On Error Resume Next
Set rng = .Range(.Cells(2, "A"), .Cells(LastRowToUse, "B")) _
.Cells.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0


RngToFix.Replace What:="000", Replacement:="", _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
MatchCase:=False


LastRowToUse = 0
For Each myCol In RngToFix.Columns
LastRowInCol = .Cells(.Rows.Count, myCol.Column).End(xlUp).Row
If LastRowInCol > LastRowToUse Then
LastRowToUse = LastRowInCol
End If
Next myCol


Set RngToFix = RngToFix.Resize(LastRowToUse - 1).Offset(2, 0)
Set rng = Nothing
On Error Resume Next
Set rng = RngToFix.Cells.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If rng Is Nothing Then
MsgBox "No blanks found"
Exit Sub
Else
rng.FormulaR1C1 = "=R[-1]C"



End If

End With

End Sub


Any assistance would be appreciated!!!
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel VBA 1
Help with Macro please... 6
Find next problem 1
Number displayed as text difficulties 4
Need error trap 4
Change Needed - VB Code in excel 2
Loop thru Range Help needed 9
Application Defined Error setting a formula via VBA 2

Top