Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding two characters in front plus three in back
Attached is my currect macro is adding two characters in the front would it
be possible to add three characters in the back also? Sub Goodman2() Const Summaryworkbook = "GOODMAN OPEN ITEMS.xls" Const MainInvoiceCol = 2 Const MainPasteCol = 22 Const wbkInvoiceCol = 5 Const wbkStartCol = 1 Const wbkEndCol = 14 Dim i As Integer Set wsh1 = Workbooks(Summaryworkbook).Worksheets(1) wsh1.Activate Lastrow = wsh1.Cells(Rows.Count, MainInvoiceCol).End(xlUp).Row Set InvoiceRange = wsh1. _ Range(Cells(1, MainInvoiceCol), Cells(Lastrow, MainInvoiceCol)) For Each cell1 In InvoiceRange InvoiceNumber = cell1.Value i = Len(InvoiceNumber) i = i + 2 InvoiceNumber = Right(InvoiceNumber, i) For Each wbk1 In Application.Workbooks If StrComp(wbk1.Name, Summaryworkbook) < 0 Then With wbk1.Worksheets(1) .Activate Lastrow = .Cells(Rows.Count, wbkInvoiceCol).End(xlUp).Row Set InvoiceRange2 = .Range(Cells(1, wbkInvoiceCol), Cells(Lastrow, wbkInvoiceCol)) For Each Cell2 In InvoiceRange2 If (Cell2.Value = "CM" & InvoiceNumber) Then .Range(Cells(Cell2.Row, wbkStartCol), _ Cells(Cell2.Row, wbkEndCol)).Copy _ Destination:=wsh1.Cells(cell1.Row, MainPasteCol) End If Next Cell2 End With End If Next wbk1 Next cell1 End Sub -- Message posted via http://www.officekb.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding two characters in front plus three in back
If I understand you correctly, one way:
Public Sub Goodman3() Const Summaryworkbook As String = "GOODMAN OPEN ITEMS.xls" Const MainInvoiceCol As Long = 2 Const MainPasteCol As Long = 22 Const wbkInvoiceCol As Long = 5 Const wbkStartCol As Long = 1 Const wbkEndCol As Long = 14 Const csTHREECHARS As String = "xxx" 'your three chars here Dim wb As Workbook Dim ws As Worksheet Dim rCell As Range Dim rCell2 As Range Dim sInvoiceNumber As String Set ws = Workbooks(Summaryworkbook).Worksheets(1) With ws For Each rCell In .Range(.Cells(1, MainInvoiceCol), _ .Cells(.Rows.Count, MainInvoiceCol).End(xlUp)) sInvoiceNumber = CStr(rCell.Value) For Each wb In Application.Workbooks If StrComp(wb.Name, Summaryworkbook) < 0 Then With wb.Worksheets(1) For Each rCell2 In .Range(.Cells(1, wbkInvoiceCol), _ .Cells(.Rows.Count, wbkInvoiceCol).End(xlUp)) If .Value = "CM" & sInvoiceNumber & csTHREECHARS Then .Range(.Cells(rCell2.Row, wbkStartCol), _ .Cells(rCell2.Row, wbkEndCol)).Copy _ Destination:=ws.Cells(rCell.Row, MainPasteCol) Exit For End If Next rCell2 End With End If Next wb Next rCell End With End Sub In article <70fd9d688f2d6@uwe, "Hinojosa via OfficeKB.com" <u27679@uwe wrote: Attached is my currect macro is adding two characters in the front would it be possible to add three characters in the back also? Sub Goodman2() Const Summaryworkbook = "GOODMAN OPEN ITEMS.xls" Const MainInvoiceCol = 2 Const MainPasteCol = 22 Const wbkInvoiceCol = 5 Const wbkStartCol = 1 Const wbkEndCol = 14 Dim i As Integer Set wsh1 = Workbooks(Summaryworkbook).Worksheets(1) wsh1.Activate Lastrow = wsh1.Cells(Rows.Count, MainInvoiceCol).End(xlUp).Row Set InvoiceRange = wsh1. _ Range(Cells(1, MainInvoiceCol), Cells(Lastrow, MainInvoiceCol)) For Each cell1 In InvoiceRange InvoiceNumber = cell1.Value i = Len(InvoiceNumber) i = i + 2 InvoiceNumber = Right(InvoiceNumber, i) For Each wbk1 In Application.Workbooks If StrComp(wbk1.Name, Summaryworkbook) < 0 Then With wbk1.Worksheets(1) .Activate Lastrow = .Cells(Rows.Count, wbkInvoiceCol).End(xlUp).Row Set InvoiceRange2 = .Range(Cells(1, wbkInvoiceCol), Cells(Lastrow, wbkInvoiceCol)) For Each Cell2 In InvoiceRange2 If (Cell2.Value = "CM" & InvoiceNumber) Then .Range(Cells(Cell2.Row, wbkStartCol), _ Cells(Cell2.Row, wbkEndCol)).Copy _ Destination:=wsh1.Cells(cell1.Row, MainPasteCol) End If Next Cell2 End With End If Next wbk1 Next cell1 End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding two characters in front plus three in back
I have a ffew commentts with your code
1) this statements in not necessary InvoiceNumber = Right(InvoiceNumber, i) Visual Basic will automatically lengthen you sttrings. 2) This statement is a test statementt and doesn't change the value of cell2.value If (Cell2.Value = "CM" & InvoiceNumber) Then I don't see where you are really adding tow characters in front of the string 3) use these type statements for adding characters to front and back of strings a = "123" b = "456" c= a + b results in "123456" d = "ab" + a results in "ab123" e = a + "ab" results in "123ab" "Hinojosa via OfficeKB.com" wrote: Attached is my currect macro is adding two characters in the front would it be possible to add three characters in the back also? Sub Goodman2() Const Summaryworkbook = "GOODMAN OPEN ITEMS.xls" Const MainInvoiceCol = 2 Const MainPasteCol = 22 Const wbkInvoiceCol = 5 Const wbkStartCol = 1 Const wbkEndCol = 14 Dim i As Integer Set wsh1 = Workbooks(Summaryworkbook).Worksheets(1) wsh1.Activate Lastrow = wsh1.Cells(Rows.Count, MainInvoiceCol).End(xlUp).Row Set InvoiceRange = wsh1. _ Range(Cells(1, MainInvoiceCol), Cells(Lastrow, MainInvoiceCol)) For Each cell1 In InvoiceRange InvoiceNumber = cell1.Value i = Len(InvoiceNumber) i = i + 2 InvoiceNumber = Right(InvoiceNumber, i) For Each wbk1 In Application.Workbooks If StrComp(wbk1.Name, Summaryworkbook) < 0 Then With wbk1.Worksheets(1) .Activate Lastrow = .Cells(Rows.Count, wbkInvoiceCol).End(xlUp).Row Set InvoiceRange2 = .Range(Cells(1, wbkInvoiceCol), Cells(Lastrow, wbkInvoiceCol)) For Each Cell2 In InvoiceRange2 If (Cell2.Value = "CM" & InvoiceNumber) Then .Range(Cells(Cell2.Row, wbkStartCol), _ Cells(Cell2.Row, wbkEndCol)).Copy _ Destination:=wsh1.Cells(cell1.Row, MainPasteCol) End If Next Cell2 End With End If Next wbk1 Next cell1 End Sub -- Message posted via http://www.officekb.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding two characters in front plus three in back
you can add blanks, a variable, or text to the end. I think this is what you
are asking. i=i & " " i=i & "" & variable i=i & ""abc" "Hinojosa via OfficeKB.com" wrote: Attached is my currect macro is adding two characters in the front would it be possible to add three characters in the back also? Sub Goodman2() Const Summaryworkbook = "GOODMAN OPEN ITEMS.xls" Const MainInvoiceCol = 2 Const MainPasteCol = 22 Const wbkInvoiceCol = 5 Const wbkStartCol = 1 Const wbkEndCol = 14 Dim i As Integer Set wsh1 = Workbooks(Summaryworkbook).Worksheets(1) wsh1.Activate Lastrow = wsh1.Cells(Rows.Count, MainInvoiceCol).End(xlUp).Row Set InvoiceRange = wsh1. _ Range(Cells(1, MainInvoiceCol), Cells(Lastrow, MainInvoiceCol)) For Each cell1 In InvoiceRange InvoiceNumber = cell1.Value i = Len(InvoiceNumber) i = i + 2 InvoiceNumber = Right(InvoiceNumber, i) For Each wbk1 In Application.Workbooks If StrComp(wbk1.Name, Summaryworkbook) < 0 Then With wbk1.Worksheets(1) .Activate Lastrow = .Cells(Rows.Count, wbkInvoiceCol).End(xlUp).Row Set InvoiceRange2 = .Range(Cells(1, wbkInvoiceCol), Cells(Lastrow, wbkInvoiceCol)) For Each Cell2 In InvoiceRange2 If (Cell2.Value = "CM" & InvoiceNumber) Then .Range(Cells(Cell2.Row, wbkStartCol), _ Cells(Cell2.Row, wbkEndCol)).Copy _ Destination:=wsh1.Cells(cell1.Row, MainPasteCol) End If Next Cell2 End With End If Next wbk1 Next cell1 End Sub -- Message posted via http://www.officekb.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding two characters in front plus three in back
it debugs on
If .Value = "CM" & sInvoiceNumber & csTHREECHARS Then JE McGimpsey wrote: If I understand you correctly, one way: Public Sub Goodman3() Const Summaryworkbook As String = "GOODMAN OPEN ITEMS.xls" Const MainInvoiceCol As Long = 2 Const MainPasteCol As Long = 22 Const wbkInvoiceCol As Long = 5 Const wbkStartCol As Long = 1 Const wbkEndCol As Long = 14 Const csTHREECHARS As String = "xxx" 'your three chars here Dim wb As Workbook Dim ws As Worksheet Dim rCell As Range Dim rCell2 As Range Dim sInvoiceNumber As String Set ws = Workbooks(Summaryworkbook).Worksheets(1) With ws For Each rCell In .Range(.Cells(1, MainInvoiceCol), _ .Cells(.Rows.Count, MainInvoiceCol).End(xlUp)) sInvoiceNumber = CStr(rCell.Value) For Each wb In Application.Workbooks If StrComp(wb.Name, Summaryworkbook) < 0 Then With wb.Worksheets(1) For Each rCell2 In .Range(.Cells(1, wbkInvoiceCol), _ .Cells(.Rows.Count, wbkInvoiceCol).End(xlUp)) If .Value = "CM" & sInvoiceNumber & csTHREECHARS Then .Range(.Cells(rCell2.Row, wbkStartCol), _ .Cells(rCell2.Row, wbkEndCol)).Copy _ Destination:=ws.Cells(rCell.Row, MainPasteCol) Exit For End If Next rCell2 End With End If Next wb Next rCell End With End Sub Attached is my currect macro is adding two characters in the front would it be possible to add three characters in the back also? [quoted text clipped - 38 lines] Next cell1 End Sub -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200704/1 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding two characters in front plus three in back
so...
a = "00" b = Len(InvoiceNumber) c = a +b d = c + "-IN" right? Joel wrote: I have a ffew commentts with your code 1) this statements in not necessary InvoiceNumber = Right(InvoiceNumber, i) Visual Basic will automatically lengthen you sttrings. 2) This statement is a test statementt and doesn't change the value of cell2.value If (Cell2.Value = "CM" & InvoiceNumber) Then I don't see where you are really adding tow characters in front of the string 3) use these type statements for adding characters to front and back of strings a = "123" b = "456" c= a + b results in "123456" d = "ab" + a results in "ab123" e = a + "ab" results in "123ab" Attached is my currect macro is adding two characters in the front would it be possible to add three characters in the back also? [quoted text clipped - 38 lines] Next cell1 End Sub -- Message posted via http://www.officekb.com |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding two characters in front plus three in back
And what error is given?
In article <70fe75233e2ee@uwe, "Hinojosa via OfficeKB.com" <u27679@uwe wrote: it debugs on If .Value = "CM" & sInvoiceNumber & csTHREECHARS Then |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding two characters in front plus three in back
Run-time error '438' :
Object doesn't support this property or method Hinojosa wrote: it debugs on If .Value = "CM" & sInvoiceNumber & csTHREECHARS Then If I understand you correctly, one way: [quoted text clipped - 42 lines] Next cell1 End Sub -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200704/1 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding two characters in front plus three in back
Ah - my mistake. Change the line to
If rCell2.Value = "CM" & sInvoiceNumber & csTHREECHARS Then In article <70feb3b1ab7b4@uwe, "Hinojosa via OfficeKB.com" <u27679@uwe wrote: Run-time error '438' : Object doesn't support this property or method Hinojosa wrote: it debugs on If .Value = "CM" & sInvoiceNumber & csTHREECHARS Then If I understand you correctly, one way: [quoted text clipped - 42 lines] Next cell1 End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Printing a two spreadsheets front and back | Excel Discussion (Misc queries) | |||
print front and back | Excel Worksheet Functions | |||
Excel Front end Acces Back end | Excel Discussion (Misc queries) | |||
Replacing last 3 characters and adding them to front | Excel Discussion (Misc queries) | |||
move '-' from back to front??? | Excel Programming |