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

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

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

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

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

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

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

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

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