Adding two characters in front plus three in back

[Hinojosa via OfficeKB.com]

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

 

[JE McGimpsey]

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

 

[Joel]

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"

 

[Brent]

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]

it debugs on

If .Value = "CM" & sInvoiceNumber & csTHREECHARS Then

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

 

[Hinojosa via OfficeKB.com]

so...
a = "00"
b = Len(InvoiceNumber)
c = a +b
d = c + "-IN"

right?

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

 

[JE McGimpsey]

And what error is given?

 

[Hinojosa via OfficeKB.com]

Run-time error '438' :

Object doesn't support this property or method

it debugs on

If .Value = "CM" & sInvoiceNumber & csTHREECHARS Then

If I understand you correctly, one way:

[quoted text clipped - 42 lines]

 

[JE McGimpsey]

Ah - my mistake. Change the line to

If rCell2.Value = "CM" & sInvoiceNumber & csTHREECHARS Then

Run-time error '438' :

Object doesn't support this property or method

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