PDA

View Full Version : Autofill down based on two conditions



Loss1003
02-16-2012, 02:26 PM
Need some assistance with vba for Auto-filling a formula in column "A" down based on two conditions.

Condition #1: Autofill formula down starts when “Claim” is located in Column “A” and ends when "totals" is located in Column "A" and repeats until the last "Claim" and "totals" is located.

I'm using the below code for a similar issue that might be a good start.


See attached worksheet.
' hiker95, 02/10/2012
Dim C As Range, firstaddress As String
Application.ScreenUpdating = False
With Columns("G")
Set C = .Find("Amount X's of", LookIn:=xlValues, LookAt:=xlWhole)
If Not C Is Nothing Then
firstaddress = C.Address
Do
With C.Offset(1)

.Value = UserForm3.am1.Value

End With
Set C = .FindNext(C)
Loop While Not C Is Nothing And C.Address <> firstaddress
End If
End With
Application.ScreenUpdating = True
End Sub

mdmackillop
02-16-2012, 04:15 PM
Sub Test()
Dim c As String, t As String
Dim r As Range
Dim FR As Range
Dim Tp As Range, Bm As Range
Dim Cnt As Long


Set r = Columns(1)
c = "Claim"
t = "TOTALS"
Cnt = Application.CountIf(r, c)

Set Tp = Cells(1, 1)
Do Until i = Cnt
Set Tp = r.Find(c, Tp)(2)
Set Bm = r.Find(t, Tp)(0)
Set FR = Range(Tp, Bm)
FR(1).AutoFill Destination:=FR, Type:=xlFillSeries
i = i + 1
Loop
End Sub

Bob Phillips
02-16-2012, 04:24 PM
Autofill needs a seed cell. Is yours just filling down from the cell under Claim?