| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complicated Macro - Someone please help.....this will definetly be a challenge!!
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Complicated Macro - Someone please help.....this will definetly be
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. -- Regards, Tom Ogilvy "Stuart" wrote: 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Complicated Macro - Someone please help.....this will definetly be
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 Tom Ogilvy wrote: 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. -- Regards, Tom Ogilvy "Stuart" wrote: 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Complicated Macro - Someone please help.....this will definetl
All I changed was what it was to find "Name" as this is not what my
data is. 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. -- Regards, Tom Ogilvy "Stuart" wrote: 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 Tom Ogilvy wrote: 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. -- Regards, Tom Ogilvy "Stuart" wrote: 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Complicated Macro - Someone please help.....this will definetly be
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 Tom Ogilvy wrote: 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. -- Regards, Tom Ogilvy "Stuart" wrote: 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Complicated Macro - Someone please help.....this will definetl
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 Tom Ogilvy wrote: 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. 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 -- Regards, Tom Ogilvy "Stuart" wrote: 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 Tom Ogilvy wrote: 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. -- Regards, Tom Ogilvy "Stuart" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Complicated formula or macro | Excel Discussion (Misc queries) | |||
| HELP with complicated macro | Excel Discussion (Misc queries) | |||
| How do I do this complicated macro??? | Excel Worksheet Functions | |||
| Complicated Challenge | Excel Discussion (Misc queries) | |||
| Macro challenge | Excel Programming | |||
![Stuart[_3_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
