Assume the sheet Data has your data
and you want your lists in sheet Sheet1 in columns A and B
Sub ABC()
Dim sh as Worksheet
set sh = Worksheets("Sheet1")
Dim rng as Range, rng1 as Range
Dim rng2 as Range, sAddr as String
sStr = "Base Sales"
with worksheets("Data")
Set rng = .Cells.Find(What:=sStr, _
After:=Range("IV65536"), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
if not rng is nothing then
sAddr = rng.Address
do
set rng1 = sh.Cells(rows.count,1).End(xlup)(2)
set rng2 = sh.Cells(rows.count,2).End(xlup)(2)
res = Application.Match(rng.offset(-1,0),sh.Columns(2),0)
if iserror(res) then
rng2 = rng.offset(-1,0)
end if
if rng.offset(-2,0).value <> "" then
rng1.Value = rng.offset(-2,0)
end if
set rng = .Cells.findNext(rng)
Loop while rng.Address <> sAddr
End if
End With
End Sub
--
Regards,
Tom Ogilvy
<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>I pull sales data reports from a database source on different Accounts,
> on different products, for different 1 week time periods, then it takes
> much time having to format the data of charting. I would like to use
> VBA to help me with the formatting. I will use a UserForm that will
> place the following information (A1= # of Accounts, A2= # of Products &
> A3= # of Data Weeks), using VBA how do I tell Excel to copy all the
> Accounts to a different sheet so I can create a Validata list and also
> all the Products. Below is an example of what I'm trying to do:
>
> A1=2
> A2=3
> A3=3
>
> ACCOUNT A
> PRODUCT 1
> Base Sales Incremental Sales
> 1-Jan-06 $51,352 $1,530
> 8-Jan-06 $65,483 $3,562
> 15-Jan-06 $70,156 $153
>
> PRODUCT 2
> Base Sales Incremental Sales
> 1-Jan-06 $0 $0
> 8-Jan-06 $65 $6
> 15-Jan-06 $4,789
> $1,569
>
> PRODUCT 3
> Base Sales Incremental Sales
> 1-Jan-06 $23 $0
> 8-Jan-06 $8,973 $1,478
> 15-Jan-06 $101,253 $2,456
>
> ACCOUNT B
> PRODUCT 1
> Base Sales Incremental Sales
> 1-Jan-06 $158
> $58
> 8-Jan-06 $158
> $36
> 15-Jan-06 $204
> $100
>
> PRODUCT 2
> Base Sales Incremental Sales
> 1-Jan-06 $0
> $0
> 8-Jan-06 $0
> $0
> 15-Jan-06 $0 $0
>
> PRODUCT 3
> Base Sales Incremental Sales
> 1-Jan-06 $99
> $2
> 8-Jan-06 $876
> $186
> 15-Jan-06 $287
> $177
>
> The results on another worksheet should look like this:
> Col A Col B
> ACCOUNT 1 PRODUCT 1
> ACCOUNT 2 PRODUCT 2
> PRODUCT 3
>
>
> Much Thanks to anyone who can assist.
>
|
Similar Threads
