Complicated Macro - Someone please help.....this will definetly be a challenge!!

S

Stuart

I have data in excel. I have a combine macro to combine a hundred
spreadsheets in excel.

This works no problem however...............

The data at the moment in excel looks like,



Name SW
Report : 6



Item Price in Currency Description
Amount1 Amount2
101101 GBP Fridge 1000.00 1000.00
170101 GBP Washing Machine 200.00 200.00
201001 GBP Fund Transfer In 300.00 300.00

201002 GBP 100.00 100.00
201151 GBP 100.00 100.00
211111 GBP 200.00 200.00
311501 GBP 200.00 200.00
402001 GBP 200.00 200.00

Name JB
19/09/2005 00:00 Currency GBP Pound Sterling Report :
6 7.00



Item Price in Currency Description
Amount1 Amount2
101101 GBP Fridge 1000.00 1000.00
170101 GBP Washing Machine 200.00 200.00
201001 GBP Fund Transfer In 300.00 300.00

201002 GBP 100.00 100.00
201151 GBP 100.00 100.00
211111 GBP 200.00 200.00
311501 GBP 200.00 200.00
402001 GBP 200.00 200.00


What I need from a macro is that everytime it finds the word "Name" in
column B it will pick up the cell to the right of that and then paste
it down the account numbers until it sees the word item and then does
the same again.

The expected output from the macro working would be :

Name SW
Report : 6



Item Price in Currency Description
Amount1 Amount2
SW101101 GBP Fridge 1000.00 1000.00
SW170101 GBP Washing Machine 200.00 200.00
SW201001 GBP Fund Transfer In 300.00 300.00
SW
SW201002 GBP 100.00 100.00
SW201151 GBP 100.00 100.00
SW211111 GBP 200.00 200.00
SW311501 GBP 200.00 200.00
SW402001 GBP 200.00 200.00
SW
Name JB
19/09/2005 00:00 Currency GBP Pound Sterling Report :
6 7.00



Item Price in Currency Description
Amount1 Amount2
JB101101 GBP Fridge 1000.00 1000.00
JB170101 GBP Washing Machine 200.00 200.00
JB201001 GBP Fund Transfer In 300.00 300.00
JB
JB201002 GBP 100.00 100.00
JB201151 GBP 100.00 100.00
JB211111 GBP 200.00 200.00
JB311501 GBP 200.00 200.00
JB402001 GBP 200.00 200.00

Sorry if this looks a mess but as long as the macro starts and stops
and then starts again this should work however I think this will be a
big challenge......I spent around 3 days trying to do this using
recorded macro but it just wont work so I give up!

Hope someone can help,

Kind Regards,

Stu
 
T

Tom Ogilvy

Sub AddName()
Dim sAddr As String
Dim v() As Range, i As Long
Dim rng As Range, rng2 As Range
Dim rng1 As Range, rng3 As Range
Set rng = Columns(2).Find( _
What:="Name", _
After:=Cells(Rows.Count, 2), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
ReDim v(1 To 1)
If Not rng Is Nothing Then
sAddr = rng.Address
Do
Set v(UBound(v)) = rng
ReDim Preserve v(1 To UBound(v) + 1)
Set rng = Columns(2).FindNext(rng)
Loop While rng.Address <> sAddr
Set v(UBound(v)) = Cells(Rows.Count, 2).End(xlUp)(2)
For i = 1 To UBound(v) - 1
Set rng1 = Range(v(i), v(i + 1))
Set rng2 = Nothing
On Error Resume Next
Set rng2 = rng1.SpecialCells(xlConstants, xlNumbers)
On Error GoTo 0
If Not rng2 Is Nothing Then
Set rng3 = Intersect(rng2.EntireRow, Columns(1))
rng3.Value = v(i).Offset(0, 1)
End If
Next
End If
End Sub

worked for me with the data you show and what you described.
 
S

Stuart

Hi Tom,

Thanks for the reply,

That macro did nothing at all,

All I changed was what it was to find "Name" as this is not what my
data is. Came up with no errors at all.

Is there something missing?

I just need the macro to find a cell and then take the cell next to
that and paste down column A until it finds the next row containing the
word "Name" (as example) and then do the same thing again.

Hope you can help,

Thanks

Stuart
 
S

Stuart

Hi again Tom,

This may make a difference to you when helping :

the column where macro is to look up is in column "F" and the cell to
be entered down column A is in column G in the cell next to where the
macro has found the word.

I then want word added to the beginning of every cell down column A
until the macro finds the word again. Then it will carry out the same
process until the end of the spreadsheet.

Does this help at all?

Thanks

Stuart
 
T

Tom Ogilvy

Maybe this will help you get it working.

This what you said you wanted:
What I need from a macro is that everytime it finds the word "Name" in
column B it will pick up the cell to the right of that and then paste
it down the account numbers until it sees the word item and then does
the same again.
Click to expand...

I would modify it according to your newly revealed changes, but there are
probably more differences which you haven't reveal. With a little effort,
you should be able to modify it.

If not, put my original code in a workbook with sample data and send it to
me at (e-mail address removed)
 
T

Tom Ogilvy

All I changed was what it was to find "Name" as this is not what my
data is.
Click to expand...

Based on your latest post, how about changing the location of where the
information is located - you think that my affect the ability to process your
data. Just a guess.
 
S

Stuart

Hi Tom,

I sent you an email,

i have tried most of today to manipulate your macro but the thing just
wouldnt do anything for me at all,

Sorry to be a pain!

Kind Regards,

Stuart Wilson
 
Top