Adding two characters in front plus three in back

  • Thread starter Hinojosa via OfficeKB.com
  • Start date
H

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
 
J

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
 
J

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

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

Hinojosa via OfficeKB.com

it debugs on

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

JE said:
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
 
H

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
 
H

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]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top