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

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

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
>
>

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
>
>

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

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

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

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

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