MID and FIND function in vba
 

With a list of state/city in column A.

Wyoming - Greybull
Indiana - South Bend
Illinois - Itasca
Texas - Mineral Wells
Wyoming - Powell
Wyoming - Cody
Texas - Houston
Illinois - Fox Valley
Texas - Waco

I want the city returned to column D, where aState is a selection in H1 DV drop down.

The code errors on the word FIND in the MID function.
The mid function works on the sheet as a formula. (with H1 cell ref, not rngC)

Sub my_Instr_Copy()
Dim LRow As Long
Dim rngC As Range
Dim aState As String, aCity As String


With Sheets("Sheet1")

LRow = .Cells(.Rows.Count, "A").End(xlUp).Row
aCity = .Cells(1, 8)

For Each rngC In .Range("A2:A" & LRow)
Set aCity = Mid(rngC, Find("- ", rngC) + 2, 99)

If InStr(rngC, aState) 0 Then
Sheets("Sheet1").Range("D" & Rows.Count).End(xlUp)(2) = aCity
End If

Next

End With
End Sub

Thanks,
Howard

MID and FIND function in vba
 

Just a hint...

Dim vData, n&, k&

'Load the state - city list into vData,
'Then split each element using the " - " as delimeter
' and dump the 2nd element into colD...

k = 2 '//1st row of data
With Sheets("Sheet1")
For n = 1 to UBound(vData)
.Cells(k, 4) = Split(vData(n, 1), " - ")(1): k = k + 1
Next 'n
End With

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

MID and FIND function in vba
 

Hi Howard,

Am Fri, 3 Jun 2016 17:16:45 -0700 (PDT) schrieb L. Howard:

Set aCity = Mid(rngC, Find("- ", rngC) + 2, 99)

Find is a function and you must write it as
Application.Find.
But in VBA it is easier to use Instr
Try it this way:

aCity = Mid(rngC, InStr(rngC, " - ") + 3, 99)


Regards
Claus B.
--
Windows10
Office 2016

MID and FIND function in vba
 

With a list of state/city in column A.

Wyoming - Greybull
Indiana - South Bend
Illinois - Itasca
Texas - Mineral Wells
Wyoming - Powell
Wyoming - Cody
Texas - Houston
Illinois - Fox Valley
Texas - Waco

I want the city returned to column D, where aState is a selection in
H1 DV drop down.

The code errors on the word FIND in the MID function.
The mid function works on the sheet as a formula. (with H1 cell ref,
not rngC)

Sub my_Instr_Copy()
Dim LRow As Long
Dim rngC As Range
Dim aState As String, aCity As String


With Sheets("Sheet1")

LRow = .Cells(.Rows.Count, "A").End(xlUp).Row
aCity = .Cells(1, 8)
Did you mean State? (since you are searching for a city within a
state!)

For Each rngC In .Range("A2:A" & LRow)
Set aCity = Mid(rngC, Find("- ", rngC) + 2, 99)
You dim'd aCity as String. You are using it here as if it was dim'd an
object!

If InStr(rngC, aState) 0 Then
Sheets("Sheet1").Range("D" & Rows.Count).End(xlUp)(2) = aCity
End If

Next

End With
End Sub

Thanks,
Howard

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

MID and FIND function in vba
 

With my_Instr_Copy() code it errors wanting an Object or Variable set, tried many different sets, but don't see it.

With my_Instr_Copy_1() code works to list all cities in column D, but the requirement is for only the cities of the state shown in cell H1's DV drop down.

Howard

Sub my_Instr_Copy()

Dim rngC As Range
Dim aState As Range
Dim aCity As Range


aState = Cells(1, 8)
Set aCity = Mid(aState, InStr(aState, " - ") + 3, 99)

With Sheets("Sheet1")

For Each rngC In .Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row)

If InStr(rngC, aState) 0 Then

Sheets("Sheet1").Range("D" & Rows.Count).End(xlUp)(2) = aCity

End If

Next

End With
End Sub


Sub my_Instr_Copy_1()
Dim vData As Variant, n&, k&

vData = Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row)

k = 2
With Sheets("Sheet1")
For n = 1 To UBound(vData)
.Cells(k, 4) = Split(vData(n, 1), " - ")(1): k = k + 1
Next 'n
End With

End Sub

MID and FIND function in vba
 

Hi Howard,

Am Sat, 4 Jun 2016 02:57:39 -0700 (PDT) schrieb L. Howard:

With my_Instr_Copy() code it errors wanting an Object or Variable set, tried many different sets, but don't see it.

Set aCity = Mid(aState, InStr(aState, " - ") + 3, 99)

aCity is a string and not an object.
try:
aCity = Mid(aState, InStr(aState, " - ") + 3, 99)

Sub Test()
Dim LRow As Long, i As Long
Dim varCity() As Variant, varTmp As Variant

With Sheets("Sheet1")
LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
varTmp = .Range("A2:A" & LRow)

ReDim Preserve varCity(UBound(varTmp))
For i = LBound(varTmp) To UBound(varTmp)
varCity(i) = Split(varTmp(i, 1), " - ")(1)
Next
.Cells(.Rows.Count, 4).End(xlUp)(2).Resize(UBound(varCity) + 1) _
= Application.Transpose(varCity)
End With
End Sub


Regards
Claus B.
--
Windows10
Office 2016

MID and FIND function in vba
 

aCity is a string and not an object.
try:
aCity = Mid(aState, InStr(aState, " - ") + 3, 99)

Sub Test()
Dim LRow As Long, i As Long
Dim varCity() As Variant, varTmp As Variant

With Sheets("Sheet1")
LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
varTmp = .Range("A2:A" & LRow)

ReDim Preserve varCity(UBound(varTmp))
For i = LBound(varTmp) To UBound(varTmp)
varCity(i) = Split(varTmp(i, 1), " - ")(1)
Next
.Cells(.Rows.Count, 4).End(xlUp)(2).Resize(UBound(varCity) + 1) _
= Application.Transpose(varCity)
End With
End Sub


Regards
Claus B.

Hi Claus,

This works to a degree, except it returns "oming" if Wyoming is selected in H1. Cuts off the first three letters of each rngC of any state selected in H1.

Your Sub Test() returns all cities in column A, where I need only the cities of the state selected in H1.

Howard

Sub my_Instr_Copy()

Dim rngC As Range
Dim aState As Range
Dim aCity As String

Set aState = Cells(1, 8)

With Sheets("Sheet1")

aCity = Mid(aState, InStr(aState, " - ") + 3, 99)

For Each rngC In .Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row)

If InStr(rngC, aState) 0 Then
Sheets("Sheet1").Range("D" & Rows.Count).End(xlUp)(2) = aCity
End If

Next

End With
End Sub

MID and FIND function in vba
 

Hi Howard,

Am Sat, 4 Jun 2016 03:55:47 -0700 (PDT) schrieb L. Howard:

Your Sub Test() returns all cities in column A, where I need only the cities of the state selected in H1.

sorry, my bad.
Try:

Sub my_Instr_Copy()
Dim rngC As Range
Dim aState As String
Dim aCity As String

aState = Cells(1, 8)

With Sheets("Sheet1")
aCity = Mid(aState, InStr(aState, " - ") + 3, 99)

For Each rngC In .Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row)
If InStr(rngC, aState) Then
Sheets("Sheet1").Range("D" & Rows.Count).End(xlUp)(2) = _
Replace(rngC, aState & " - ", "")
End If
Next

End With
End Sub


Regards
Claus B.
--
Windows10
Office 2016

MID and FIND function in vba
 

Hi Howard,

Am Sat, 4 Jun 2016 13:16:16 +0200 schrieb Claus Busch:

Try:

Sub my_Instr_Copy()

if you don't have always a leading and a trailing space around the
hyphen better try:

Sub my_Instr_Copy()
Dim rngC As Range
Dim aState As String
Dim aCity As String

aState = Cells(1, 8)

With Sheets("Sheet1")
aCity = Mid(aState, InStr(aState, " - ") + 3, 99)

For Each rngC In .Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row)
If InStr(rngC, aState) Then
Sheets("Sheet1").Range("D" & Rows.Count).End(xlUp)(2) = _
Application.Trim(Split(rngC, "-")(1))
End If
Next

End With
End Sub


Regards
Claus B.
--
Windows10
Office 2016

MID and FIND function in vba
 

Revised to suit...

Dim sState$, vData, vTmp, n&, k&

'Load the state - city list into vData,
'Then split each element using the " - " as delimeter
' and dump the 2nd element into colD...

k = 2 '//1st row of data
With Sheets("Sheet1")
sState = .Cells(1, 8).Value
For n = 1 to UBound(vData)
vTmp = Split(vData(n, 1), " - ")(1): k = k + 1
If vTmp(0) = sState Then .Cells(k, 4) = vTmp(1)
Next 'n
End With

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

MID and FIND function in vba
 

Oops.., mis-located row counter. Fixed here...

Dim sState$, vData, vTmp, n&, k&

'Load the state - city list into vData,
'Then split each element using the " - " as delimeter
' and dump the 2nd element into colD if match...

k = 2 '//1st row of data
With Sheets("Sheet1")
sState = .Cells(1, 8).Value
For n = 1 to UBound(vData)
vTmp = Split(vData(n, 1), " - ")(1)
If vTmp(0) = sState Then .Cells(k, 4) = vTmp(1)
k = k + 1
Next 'n
End With

Optionally, if data starts in row2...
Dim sState$, vData, vTmp, n&
With Sheets("Sheet1")
sState = .Cells(1, 8).Value
For n = 1 to UBound(vData)
vTmp = Split(vData(n, 1), " - ")(1)
If vTmp(0) = sState Then .Cells(n + 1, 4) = vTmp(1)
Next 'n
End With

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

MID and FIND function in vba
 

On Saturday, June 4, 2016 at 4:16:28 AM UTC-7, Claus Busch wrote:
Hi Howard,

Am Sat, 4 Jun 2016 03:55:47 -0700 (PDT) schrieb L. Howard:

Your Sub Test() returns all cities in column A, where I need only the cities of the state selected in H1.

sorry, my bad.
Try:

Sub my_Instr_Copy()
Dim rngC As Range
Dim aState As String
Dim aCity As String

aState = Cells(1, 8)

With Sheets("Sheet1")
aCity = Mid(aState, InStr(aState, " - ") + 3, 99)

For Each rngC In .Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row)
If InStr(rngC, aState) Then
Sheets("Sheet1").Range("D" & Rows.Count).End(xlUp)(2) = _
Replace(rngC, aState & " - ", "")
End If
Next

End With
End Sub


Regards
Claus B.

Hi Claus,

This works fine, as does the "no space around hyphen" code.

Thanks much!

Howard

MID and FIND function in vba
 

Hi Garry,

I get a Type Missmatch on the vTmp(1). The value of vTmp in the Dim statement shows correctly, as does the = vTmp in the code, with the (1) removed.

vTmp(1) = type missmatch
vTmp = "Greybull" but still type missmatch

Howard


'/ revised Garry
Sub my_Instr_Copy_Garry_2()

Dim sState$, vData, vTmp, n&

vData = Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row)

With Sheets("Sheet1")
sState = .Cells(1, 8).Value

For n = 1 To UBound(vData)
vTmp = Split(vData(n, 1), " - ")(1)

If vTmp(0) = sState Then .Cells(n + 1, 4) = vTmp(1)

Next 'n

End With
End Sub

MID and FIND function in vba
 

Oops, forgot to remove the element returned from the original code.
Initially, that version assigned the city for each row. This version
loads each value into vTmp as corrected below...

Hi Garry,

I get a Type Missmatch on the vTmp(1). The value of vTmp in the Dim
statement shows correctly, as does the = vTmp in the code, with the
(1) removed.

vTmp(1) = type missmatch
vTmp = "Greybull" but still type missmatch

Howard


'/ revised Garry
Sub my_Instr_Copy_Garry_2()

Dim sState$, vData, vTmp, n&

vData = Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row)

With Sheets("Sheet1")
sState = .Cells(1, 8).Value

For n = 1 To UBound(vData)

vTmp = Split(vData(n, 1), " - ")

If vTmp(0) = sState Then .Cells(n + 1, 4) = vTmp(1)

Next 'n

End With
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

MID and FIND function in vba
 

On Saturday, June 4, 2016 at 2:56:31 PM UTC-7, GS wrote:
Oops, forgot to remove the element returned from the original code.
Initially, that version assigned the city for each row. This version
loads each value into vTmp as corrected below...


Hi Garry,

Did you forget to add the corrected code, or am I missing it somewhere here in the thread?

Howard

MID and FIND function in vba
 

On Saturday, June 4, 2016 at 2:56:31 PM UTC-7, GS wrote:
Oops, forgot to remove the element returned from the original code.
Initially, that version assigned the city for each row. This version
loads each value into vTmp as corrected below...


Hi Garry,

Did you forget to add the corrected code, or am I missing it
somewhere here in the thread?

Howard

It's 1 line in the thread...

sb:
vTmp = Split(vData(n, 1), " - ")

not:
vTmp = Split(vData(n, 1), " - ")(1)

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

MID and FIND function in vba
 

It's 1 line in the thread...

sb:
vTmp = Split(vData(n, 1), " - ")

not:
vTmp = Split(vData(n, 1), " - ")(1)

--
Garry



Aha! Okay, and of course that did fix it.

Many thanks.

Howard

MID and FIND function in vba
 

It's 1 line in the thread...

sb:
vTmp = Split(vData(n, 1), " - ")

not:
vTmp = Split(vData(n, 1), " - ")(1)

--
Garry



Aha! Okay, and of course that did fix it.

Many thanks.

Howard

You're welcome!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus