Identifying if a specific Text is found as part of the text in a cell

Mozzz

Board Regular
Joined
May 30, 2011
Messages
66
I am trying to determine if "UD" is found in a range of cells. So if the text "11 UD 2000" was in the text then the variable UDFound is true. The spacing of the UD could change so I need it test the entire string in the cell.
Here is what I have :
Code:
Sub CalculateFloorPlanDetails()
'
' CalculateFloorPlanDetails Macro
' Totals International1067 Form into New / Used Interest - New / Used Flat Fee
'
Cells.UnMerge
FirstColumn = Cells(14, Columns.Count).End(xlToLeft).Column
FirstRow = Cells(1, 18).End(xlDown).Row
FinalRow = Cells(Rows.Count, 16).End(xlUp).Row
For i = FirstRow + 2 To FinalRow
    If (Len(Cells(i, 5))) = 17 Then
        If Cells(i, 4).Value = "*UD*" Then
            TotalUD = TotalUD + Cells(i, 18)
            TotalUDCount = TotalUDCount + 1
       Else
 
            TotalNew = TotalNew + Cells(i, 18)
            TotalNewCount = TotalNewCount + 1
        End If
    Else
 
        TotalUsed = TotalUsed + Cells(i, 18)
        TotalUsedCount = TotalUsedCount + 1
    End If
 
 Next i
 
MsgBox "Total New Amount is " & TotalNew _
        & " and Total Count is " & TotalNewCount
 
MsgBox "Total UD Amount is " & TotalUD _
        & " and Total Count is " & TotalUDCount
MsgBox "Total Used Amount is " & TotalUsed _
        & " and Total Count is " & TotalUsedCount
 
End Sub

It just goes by the UD test and gives me the new and used.
Here is a small sample of the data:
<TABLE style="WIDTH: 585pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=779><COLGROUP><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2523" width=69><COL style="WIDTH: 35pt; mso-width-source: userset; mso-width-alt: 1682" width=46><COL style="WIDTH: 94pt; mso-width-source: userset; mso-width-alt: 4571" width=125><COL style="WIDTH: 111pt; mso-width-source: userset; mso-width-alt: 5412" width=148><COL style="WIDTH: 114pt; mso-width-source: userset; mso-width-alt: 5558" width=152><COL style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 3510" width=96><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 52pt; HEIGHT: 15pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl63 height=20 width=69></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 35pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl63 width=46></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 94pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl63 width=125></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 111pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl63 width=148>PROSTAR + 6X4L</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 114pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl63 width=152>1HSDJSJR3CJ611511</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 72pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl64 width=96>7/27/2011</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl64 width=75>10/25/2011</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 51pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl64 width=68>8/1/2012</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 52pt; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl63 height=20 width=69></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 35pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl63 width=46></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 94pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl63 width=125></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 111pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl63 width=148>PROSTAR + 6X4L</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 114pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl63 width=152>1HSDJSJR5CJ611512</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 72pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl64 width=96>7/29/2011</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl64 width=75>10/27/2011</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 51pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl64 width=68>8/1/2012</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 52pt; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl63 height=20 width=69></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 35pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl63 width=46></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 94pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl63 width=125></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 111pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl63 width=148>11 UD 2600R</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 114pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl63 width=152>JNAA410H5BAR10179</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 72pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl64 width=96>12/13/2010</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl64 width=75>12/15/2010</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 51pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl64 width=68>1/1/2012</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 52pt; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl63 height=20 width=69></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 35pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl63 width=46></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 94pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl63 width=125></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 111pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl63 width=148>11 UD2000 N</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 114pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl63 width=152>JNAL310H5BAN10022</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 72pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl64 width=96>2/23/2011</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl64 width=75>2/28/2011</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 51pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl64 width=68>12/1/2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 52pt; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl63 height=20 width=69></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 35pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl63 width=46></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 94pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl63 width=125></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 111pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl63 width=148>11 UD2000 N</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 114pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl63 width=152>JNAL310H6BAN10031</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 72pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl64 width=96>5/12/2011</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl64 width=75>5/16/2011</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 51pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl64 width=68>12/1/2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 52pt; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl63 height=20 width=69></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 35pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl63 width=46></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 94pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl63 width=125>17</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 111pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl63 width=148>11 UD2000 N</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 114pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl63 width=152>JNAL310H9BAN10041</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 72pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl64 width=96>5/12/2011</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl64 width=75>5/16/2011</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 51pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl64 width=68>12/1/2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 52pt; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl63 height=20 width=69></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 35pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl63 width=46></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 94pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl63 width=125></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 111pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl63 width=148>04 INT 4400 REG CA</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 114pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl63 width=152>G84354J017686</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 72pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl64 width=96>7/29/2011</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl64 width=75>7/29/2011</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 51pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl64 width=68>2/1/2012</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 52pt; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl63 height=20 width=69>2076</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 35pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl63 width=46></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 94pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl63 width=125></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 111pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl63 width=148>05 INT 9400i6X4</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 114pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl63 width=152>G84155C052272</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 72pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl64 width=96>8/31/2011</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl64 width=75>8/31/2011</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 51pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl64 width=68>4/1/2012</TD></TR></TBODY></TABLE>

Thanks,

Mozzz
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,050
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Umm,
where does the 17 and the 2076 come from ??
New and used what ??
Should the UD count be 4 ?
 

b.downey

Active Member
Joined
Oct 16, 2011
Messages
484
Try using int "instr" function...

For example
Code:
If instr(Cells(i, 4).Value , "UD") > 0 then
 

Mozzz

Board Regular
Joined
May 30, 2011
Messages
66
The report specifies the entire vin # for the vehicle if it is new, thus Len = 17 catches the new. The used vehicles are less then 17. The manufacturer Nissan UD New (Len = 17) needs to be separated from the rest of the New. The count you see of course would be 4. This is just a small portion of the report. I am solving for count and amount. Manually this takes about 10 minutes to do. Should be able to get it done in seconds once I solve this problem.

I need the amounts to add if it is new UD separately from new everything else. The best way to do this would be if I could use the if statement to see if the cell has "UD" anything in it. The report is posted to our accounting department as New International, New UD and Usd Truck Floor Plan Interest.

Don't see a 2076, maybe you mean 1067 which is the report number.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,623
Messages
5,625,938
Members
416,143
Latest member
JoyceMB

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top