assign variable

[Guest]

If a cell in a sheet is "LL-CP-0163" how can i set a variable in a macro
equal to 0163 or the number part of that cell. also how can i set another
variable equal to 0164 or the previous variable + 1

 

[Norman Jones]

Hi Tim.

One way:

Sub Atester()
Dim sStr As String
Dim pos As Long
Dim i As Long

sStr = "CP - 163"
pos = InStr(sStr, "-")
i = Mid(sStr, pos + 1) + 1
MsgBox i
End Sub

 

[Bob Phillips]

If there are 2 - as per the example, use InstrRev

 

[Norman Jones]

Hi Bob,

Yes, mia culpa, the consequence of not using the OP's sanple data.!

Thank you.

 

[Guest]

This looks good Norman but will
sStr = "CP - 163"
pos = InStr(sStr, "-")
take into acount that "CP - 163" is actually"LL-CP - 163" ?
the other ? is how can i use the variables "pos"=(163?) and "i"=(164) in
such code as

Windows("LL-CP-"pos".xls").Activate
Range("F3:H3").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Completions LL Register 2004-09-030.xls").Activate
Range("F"i"").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Windows("LL-CP-"pos".xls").Activate
Range("B12:J18").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Completions LL Register 2004-09-030.xls").Activate
Range("G"i"").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

 

[Bob Phillips]

It's a pleasure Norman

--

HTH

RP

Hi Bob,

Yes, mia culpa, the consequence of not using the OP's sanple data.!

Thank you.

 

[Guest]

Not sure what you mean Bob. i need two variables one which is greater than
the other by +1. The first variable needs to come from the number part of
LL-CP-0163 which is assigned to a cell and the other would be 164. similar is
i use LL-CP-0190 the second variable would be 191. Sorry i am not the best
with code.

 

[Norman Jones]

Hi Tim,

As Bob indicates, I mis-read your data. Given that the required numeric
string follows the second hyphen character in your data, the InsrRev
function should be used. The suggested macro thus becomes:

Sub Atester()
Dim sStr As String
Dim pos As Long
Dim i As Long

sStr = "CP - 163"
pos = InStrRev(sStr, "-")
i = Mid(sStr, pos + 1) + 1
MsgBox i

End Sub

 

[Norman Jones]

Hi Tim,

See my adjusted routine in the adjacent post.

Fortunately, Bob had already spotted that I had incorrectly read the
structure of your data strings.

 

[Guest]

Thanks Norman heres what i have but it is not coded correct, here is what i
am trying to do thinking i = # and j = # -1 and what is "MsgBox i".

' Macro recorded 9/30/2004 by Husky User
'


Sub Atester()
Dim sStr As String
Dim pos As Long
Dim i As Long
Dim j As Long
Windows"current sheet".Activate
Range("J2").Select
sStr = "J2"
pos = InStrRev(sStr, "-")
i = Mid(sStr, pos + 1) + 1
j = i - 1
MsgBox i
MsgBox j

Windows("LL-CP-j.xls").Activate
Range("F3:H3").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Completions LL Register 2004-09-030.xls").Activate
Range("F"i"").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End Sub

 

[Norman Jones]

Hi Tim,

I used a MsgBox in my code merely to give you visual confirmation that the
correct value had been assigned to the i variable. It serves no other
purpose here and can be deleted.

Whilst I do not know the layout or structure of your workbooks and data.,
there are a number of syntax problems in your code. Additionally, you make
repeated selections/activations. These are rarely necessary.

Revising your code to deal with these issues, we have:

Sub Atester()
Dim sStr As String
Dim pos As Long
Dim i As Long
Dim j As Long

sStr = Sheets("current sheet").Range("J2").Value
pos = InStrRev(sStr, "-")
i = Mid(sStr, pos + 1) + 1
j = i - 1
Workbooks("LL-CP-j.xls").Sheets("SheetYYY"). _
Range("F3:H3").Copy
Workbooks("Completions LL Register 2004-09-030.xls"). _
Sheets("SheetXXX").Range("F" & i). _
PasteSpecial Paste:=xlValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
End Sub

You need to replace SheetXXX and SheetYYY with the appropriate sheet names.

Also, do you really have a workshheet named "Current Sheet"? If you do,
fine. If, however, you are endeavouring to reference the active worksheet,
replace the line:

sStr = Sheets("current sheet").Range("J2").Value

with

sStr = ActiveSheet.Range("J2").Value

Lastly, You have introduced a new variable j but there appears to be no use
made of the variable.