Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default 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

  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default 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

  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default 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


  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default 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
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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
  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default 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



  #11   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default 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

  #12   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default 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
  #13   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default 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

  #14   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default 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

  #15   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default 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


  #16   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default 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

  #17   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default 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
  #18   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default 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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
If find function to not find anything Carlos Excel Programming 4 February 14th 08 10:25 AM
Find function using chr gazza67[_2_] Excel Programming 1 September 13th 07 08:42 AM
Need to find a function. MikeCampbell Excel Worksheet Functions 2 January 29th 07 02:40 AM
Find Function kiza[_4_] Excel Programming 2 June 11th 04 02:49 PM
backwards find function to find character in a string of text Ashleigh K. Excel Programming 1 January 14th 04 05:36 PM


All times are GMT +1. The time now is 07:58 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"