LinkBack URL
About LinkBacks
Thanks for the continued help Tom. The macro nearly works correctly now, it moves the costs from the sheet carriage to the sheet customers which is great.
The second part of the macro was to highlight customers on the sheet carriage which did not appear on the sheet customer. At the moment it highlights the customers on the sheet customer that don't appear on the sheet carriage. I have tried to change this but have had no success!
Posts so far for the problem are below:
Macro error 2
--------------------------------------------------------------------------------
I posted the below post:
I have two sheets, one with a customer name and corresponding carriage cost (named carriage) and one with customer name and many other columns of information including a blank space for carriage cost (named customer.) The customer names are in different orders on the two different sheets.
I have written the below code which copies the carriage cost from sheet carriage into sheet customer which works until a customer that is on the carriage sheet is not on the customer sheet. I would like the macro to highlight the customer on the carriage sheet that is not on the customer sheet and then continue with the macro.
Any ideas? Thank you for your help. Code below.
Sub carriagemove()
'
' carriagemove Macro
' Macro recorded 14/07/2005 by James Fuggle
'
'
Dim swop As String
Dim rw As String
Sheets("Carriage").Select
Range("A300").Select
ActiveCell.FormulaR1C1 = "Grand total"
Range("A1").Select
Cells.Find(What:="Grand total", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
Application.CutCopyMode = False
rw = ActiveCell.Row
Range("A1").Select
Do While ActiveCell.Row < rw
Sheets("Carriage").Select
ActiveCell.Offset(1, 0).Select
swop = ActiveCell
ActiveCell.Offset(0, 1).Select
Selection.Copy
Sheets("Customers").Select
Cells.Find(What:=swop, After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False).Activate
Application.CutCopyMode = False
If ActiveCell.Row >= rw Then Exit Do
ActiveCell.Offset(0, 7).Select
Sheets("Carriage").Select
Selection.Copy
Sheets("Customers").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("Carriage").Select
ActiveCell.Offset(0, -1).Select
Loop
End Sub
And I received the below reply
sub AAA()
Dim rng1 as Range, rng2 as Range, rng3 as Range
Dim cell as Range, swop as String
with worksheets("Carriage")
set rng1 = .Range(.Cells(2,1),.Cells(2,1).End(xldown))
end with
With worksheets("Customer")
set rng2 = .Range(.Cells(1,1),.Cells(1,1).End(xldown))
End With
rng1.Interior.colorIndex = xlNone
for each cell in rng1
swop = cell.value
set rng3 = rng2.Find(What:=swop, After:=rng2(1), LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
if not rng3 is nothing then
rng3.offset(0,7).Value = cell.offset(0,1).Value
else
cell.Interior.ColorIndex = 3
end if
Next
End Sub
This unfortunately does not work correctly. It puts the costs in for the first customer un the customer sheet then makes all the other customer names red in the carriage sheet although they are on the customer sheet.
James Fuggle
fugfug
View Public Profile
Send a private message to fugfug
Find all posts by fugfug
Add fugfug to Your Buddy List
#2 Yesterday, 08:05 PM
Tom Ogilvy
Guest Posts: n/a
Re: Macro error 2
--------------------------------------------------------------------------------
I believe I had my sheets reversed. See if this works. It assumes you have
a header in row1 of each sheet.
Sub AAA()
Dim rng1 As Range, rng2 As Range, rng3 As Range
Dim cell As Range, swop As String
With Worksheets("Carriage")
Set rng2 = .Range(.Cells(2, 1), .Cells(2, 1).End(xlDown))
End With
With Worksheets("Customer")
Set rng1 = .Range(.Cells(2, 1), .Cells(2, 1).End(xlDown))
End With
rng1.Interior.ColorIndex = xlNone
For Each cell In rng1
swop = cell.Value
Set rng3 = rng2.Find(What:=swop, After:=rng2(1), LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If Not rng3 Is Nothing Then
cell.Offset(0, 7).Value = rng3.Offset(0, 1).Value
Else
cell.Interior.ColorIndex = 3
End If
Next
End Sub
--
Regards,
Tom Ogilvy
"fugfug" <fugfug.1ti8f5_1123604061.7525@excelforum-nospam.com> wrote in
message news:fugfug.1ti8f5_1123604061.7525@excelforum-nospam.com...
>
> I posted the below post:
>
> I have two sheets, one with a customer name and corresponding carriage
> cost (named carriage) and one with customer name and many other columns
> of information including a blank space for carriage cost (named
> customer.) The customer names are in different orders on the two
> different sheets.
>
> I have written the below code which copies the carriage cost from sheet
> carriage into sheet customer which works until a customer that is on the
> carriage sheet is not on the customer sheet. I would like the macro to
> highlight the customer on the carriage sheet that is not on the
> customer sheet and then continue with the macro.
>
> Any ideas? Thank you for your help. Code below.
>
> Sub carriagemove()
> '
> ' carriagemove Macro
> ' Macro recorded 14/07/2005 by James Fuggle
> '
>
> '
> Dim swop As String
> Dim rw As String
>
> Sheets("Carriage").Select
> Range("A300").Select
> ActiveCell.FormulaR1C1 = "Grand total"
> Range("A1").Select
> Cells.Find(What:="Grand total", After:=ActiveCell, LookIn:=xlFormulas,
> _
> LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
> MatchCase:=False).Activate
> Application.CutCopyMode = False
>
> rw = ActiveCell.Row
> Range("A1").Select
>
> Do While ActiveCell.Row < rw
> Sheets("Carriage").Select
>
> ActiveCell.Offset(1, 0).Select
>
> swop = ActiveCell
>
> ActiveCell.Offset(0, 1).Select
> Selection.Copy
>
> Sheets("Customers").Select
>
> Cells.Find(What:=swop, After:=ActiveCell, LookIn:= _
> xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
> xlNext, MatchCase:=False).Activate
> Application.CutCopyMode = False
>
> If ActiveCell.Row >= rw Then Exit Do
>
> ActiveCell.Offset(0, 7).Select
>
> Sheets("Carriage").Select
> Selection.Copy
>
> Sheets("Customers").Select
> Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
> _
> False, Transpose:=False
>
> Sheets("Carriage").Select
> ActiveCell.Offset(0, -1).Select
>
> Loop
>
> End Sub
>
> And I received the below reply
>
> sub AAA()
> Dim rng1 as Range, rng2 as Range, rng3 as Range
> Dim cell as Range, swop as String
> with worksheets("Carriage")
> set rng1 = .Range(.Cells(2,1),.Cells(2,1).End(xldown))
> end with
> With worksheets("Customer")
> set rng2 = .Range(.Cells(1,1),.Cells(1,1).End(xldown))
> End With
> rng1.Interior.colorIndex = xlNone
> for each cell in rng1
> swop = cell.value
> set rng3 = rng2.Find(What:=swop, After:=rng2(1), LookIn:= _
> xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
> SearchDirection:=xlNext, MatchCase:=False)
> if not rng3 is nothing then
> rng3.offset(0,7).Value = cell.offset(0,1).Value
> else
> cell.Interior.ColorIndex = 3
> end if
> Next
> End Sub
>
> This unfortunately does not work correctly. It puts the costs in for
> the first customer un the customer sheet then makes all the other
> customer names red in the carriage sheet although they are on the
> customer sheet.
>
> James Fuggle
Register To Reply
Bookmarks