InStr ... If and Then

Status
Not open for further replies.

alexoigres

Board Regular
Joined
Dec 2, 2005
Messages
184
Hello Everybody

I have a problem with my macro. I'm pasting some part of my macro here that i think is the problem.
A friend of my help me with this macro and this funtion INSTR.
50% of the macro is working ok, but the rest of the macro is not working.



Case "KKT02"
bookCode = "RT"

If ((InStr(series, "U") <> 0) And (InStr(series, "C") <> 0) And (InStr(series, "P") <> 0)) Then
'GMDAT series applies for US Chevy RT, Canada Chevy RT, and Canada Pontiac RT

'Insert US Chevy RT row
Call getnorUSChevyRTConversionOptions
Call getRTDivCntrSeriesBody("1", "UCM")
Call insertrowtest(6, count)
Call insertFormattedData

'Increase counter
count = count + 1

'Original row is for Canada Pontiac RT
div = "2"
cntr = "C"
Call changeCanadaPontiacYear
Call getnorCanadaPontiacRTConversionOptions

ElseIf ((InStr(series, "U") <> 0) And (InStr(series, "C") <> 0)) Then
'GMDAT series applies for US Chevy RT, and Canada Chevy RT

'Insert US Chevy RT row
Call getnorUSChevyRTConversionOptions
Call getRTDivCntrSeriesBody("DEL", "UCM")
Call insertrowtest(3, count)
Call insertFormattedData

'Increase counter
count = count + 1

'Original row is for Canada Chevy RT
div = "1"
cntr = "UCM"
Call getnorCanadaChevyRTConversionOptions

ElseIf ((InStr(series, "U") <> 0) And (InStr(series, "P") <> 0)) Then
'GMDAT series applies for US Chevy RT, and Canada Pontiac RT

'Insert US Chevy RT row
Call getnorUSChevyRTConversionOptions
Call getRTDivCntrSeriesBody("1", "UM")
Call insertrowtest(6, count)
Call insertFormattedData

'Increase counter
count = count + 1

'Original row is for Canada Pontiac RT
div = "2"
cntr = "C"
Call changeCanadaPontiacYear
Call getnorCanadaPontiacRTConversionOptions

ElseIf ((InStr(series, "C") <> 0) And (InStr(series, "P") <> 0)) Then
'GMDAT series applies for Canada Chevy RT, and Canada Pontiac RT

'Insert Canada Chevy RT
Call getnorCanadaChevyRTConversionOptions
Call getRTDivCntrSeriesBody("1", "CM")
Call insertrowtest(6, count)
Call insertFormattedData

'Increase counter
count = count + 1

'Original row is for Canada Pontiac RT
div = "2"
cntr = "C"
Call changeCanadaPontiacYear
Call getnorCanadaPontiacRTConversionOptions

ElseIf (InStr(series, "P") <> 0) Then
'GMDAT series applies for Canada Pontiac RT
Call getnorCanadaPontiacRTConversionOptions
div = "2"
cntr = "C"

ElseIf (InStr(series, "U") <> 0) Then
'GMDAT series applies for US Chevy RT
getnorUSChevyRTConversionOptions
div = "1"
cntr = "UM"

ElseIf (InStr(series, "C") <> 0) Then
'GMDAT series applies for Canada Chevy RT
Call getnorCanadaChevyRTConversionOptions
div = "1"
cntr = "CM"

Else
div = "Check series: " & series

End If
series = "TD"
body = Replace(body, "4N", "69")
body = Replace(body, "5H", "48")

When I insert the row and i call these function for the original they are not working....
getnorCanadaPontiacRTConversionOptions
getnorCanadaChevyRTConversionOptions
but...getnorUSChevyRTConversionOptions
works perfect... and I'm using the same logic for the other 2.

Private Function getnorUSChevyRTConversionOptions()

If ((InStr(series, "ULS") <> 0) And (InStr(series, "ULT") <> 0) And (InStr(series, "US") <> 0)) Then
'Usage for T-Car BASE, LS, and LT models - no rpo modification required

ElseIf ((InStr(series, "ULS") <> 0) And (InStr(series, "ULT") <> 0)) Then
'Usage for T-Car LS and LT models - no rpo modification required

ElseIf ((InStr(series, "US") <> 0) And (InStr(series, "ULT") <> 0)) Then
'Usage for T-Car BASE and LT model - requires positive "JCA" in RPO bucket
gmrpo = "&JCA" & gmrpo

ElseIf ((InStr(series, "ULS") <> 0) And (InStr(series, "US") <> 0)) Then
'Usage for T-Car LS and BASE model - requires negative "JCA" in RPO bucket
Call addBodyRPO("/JCA")

ElseIf (InStr(series, "ULS") <> 0) Then
'Usage for T-Car LS model - requires negative "JCA" in RPO bucket
Call addBodyRPO("/JCA")

ElseIf (InStr(series, "ULT") <> 0) Then
'Usage for T-Car LT model - requires positive "&JCA" in RPO bucket
gmrpo = "&JCA" & gmrpo

ElseIf (InStr(series, "US") <> 0) Then
'Usage for T-Car LS model - requires negative "JCA" in RPO bucket
Call addBodyRPO("/JCA")

End If

End Function

Private Sub getnorCanadaChevyRTConversionOptions()

If ((InStr(series, "CLS") <> 0) And (InStr(series, "CLT") <> 0)) Then
'Usage for T-Car LS and LT models - no rpo modification required

ElseIf (InStr(series, "CLS") <> 0) Then
'Usage for T-Car LS model - requires negative "JCA" in RPO bucket
Call addBodyRPO("/JCA")

ElseIf (InStr(series, "CLT") <> 0) Then
'Usage for T-Car LT model - requires positive "&JCA" in RPO bucket
gmrpo = "&JCA" & gmrpo

End If

End Sub

Private Sub getnorCanadaPontiacRTConversionOptions()

If ((InStr(series, "PLS") <> 0) And (InStr(series, "PLT") <> 0)) Then
'Usage for T-Car LS, and LT models - no rpo modification required

ElseIf (InStr(series, "PLS") <> 0) Then
'Usage for T-Car LS model - requires negative "JCB" in RPO bucket
Call addBodyRPO("/JCB")

ElseIf (InStr(series, "PLT") <> 0) Then
'Usage for T-Car LT model - requires positive "&JCB" in RPO bucket
gmrpo = "&JCB" & gmrpo

End If

End Sub

This is and example what i get:

<table border="1" bordercolor="#C0C0C0" bordercolordark="#FFFFFF" cellspacing="0" cellpadding="2"> <tr> <th align="right" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></th> <th align="right" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></th> <th valign="bottom"><font face="Tahoma" size="2"><nobr>MY MACRO</nobr></font></th> <th align="right" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></th> <th align="right" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></th> <th valign="bottom"><font face="Tahoma" size="2"><nobr>I SHOULD GET</nobr></font></th> <th align="right" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></th> </tr> <tr> <td bgcolor="#FFFF00" align="right" valign="bottom"><font face="Tahoma" size="2"><nobr>96464829</nobr></font></td> <td bgcolor="#FFFF00" valign="bottom"><font face="Tahoma" size="2"><nobr>ULT,PLS,PLT</nobr></font></td> <td bgcolor="#FFFF00" valign="bottom"><font face="Tahoma" size="2"><nobr>&JCA&L91&ML4&B19&GH4&D8R&BMF</nobr></font></td> <td bgcolor="#FFFF00" align="right" valign="bottom"><font face="Tahoma" size="2"><nobr>1</nobr></font></td> <td align="right" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td> <td bgcolor="#FFFF00" valign="bottom"><font face="Tahoma" size="2"><nobr>&JCA&L91&ML4&B19&GH4&D8R&BMF</nobr></font></td> <td bgcolor="#FFFF00" valign="bottom"><font face="Tahoma" size="2"><nobr>GOOD</nobr></font></td> </tr> <tr> <td bgcolor="#FFFFFF" align="right" valign="bottom"><font face="Tahoma" size="2"><nobr>96464829</nobr></font></td> <td bgcolor="#FFFFFF" valign="bottom"><font face="Tahoma" size="2"><nobr>ULT,PLS,PLT</nobr></font></td> <td bgcolor="#FFFFFF" valign="bottom"><font face="Tahoma" size="2"><nobr>&JCA&L91&ML4&B19&GH4&D8R&BMF</nobr></font></td> <td bgcolor="#FFFFFF" align="right" valign="bottom"><font face="Tahoma" size="2"><nobr>2</nobr></font></td> <td align="right" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td> <td bgcolor="#FFFFFF" valign="bottom"><font face="Tahoma" size="2"><nobr>&L91&ML4&B19&GH4&D8R&BMF</nobr></font></td> <td bgcolor="#FFFFFF" valign="bottom"><font face="Tahoma" size="2"><nobr>BAD (no JCA)</nobr></font></td> </tr> <tr> <td bgcolor="#FFFFFF" align="right" valign="bottom"><font face="Tahoma" size="2"><nobr>96832759</nobr></font></td> <td bgcolor="#FFFFFF" valign="bottom"><font face="Tahoma" size="2"><nobr>PLS,PLT</nobr></font></td> <td bgcolor="#FFFFFF" valign="bottom"><font face="Tahoma" size="2"><nobr>&L91&ML4&B19&GH4&D8R&BMF</nobr></font></td> <td bgcolor="#FFFFFF" align="right" valign="bottom"><font face="Tahoma" size="2"><nobr>2</nobr></font></td> <td align="right" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td> <td bgcolor="#FFFFFF" valign="bottom"><font face="Tahoma" size="2"><nobr>&L91&ML4&B19&GH4&D8R&BMF</nobr></font></td> <td bgcolor="#FFFFFF" valign="bottom"><font face="Tahoma" size="2"><nobr>GOOD</nobr></font></td> </tr> <tr> <td bgcolor="#FFFF00" align="right" valign="bottom"><font face="Tahoma" size="2"><nobr>96537407</nobr></font></td> <td bgcolor="#FFFF00" valign="bottom"><font face="Tahoma" size="2"><nobr>CLS,PLS</nobr></font></td> <td bgcolor="#FFFF00" valign="bottom"><font face="Tahoma" size="2"><nobr>&82I-B19&VED/JCA</nobr></font></td> <td bgcolor="#FFFF00" align="right" valign="bottom"><font face="Tahoma" size="2"><nobr>1</nobr></font></td> <td align="right" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td> <td bgcolor="#FFFF00" valign="bottom"><font face="Tahoma" size="2"><nobr>&82I-B19&VED/JCA</nobr></font></td> <td bgcolor="#FFFF00" valign="bottom"><font face="Tahoma" size="2"><nobr>GOOD</nobr></font></td> </tr> <tr> <td bgcolor="#FFFFFF" align="right" valign="bottom"><font face="Tahoma" size="2"><nobr>96537407</nobr></font></td> <td bgcolor="#FFFFFF" valign="bottom"><font face="Tahoma" size="2"><nobr>CLS,PLS</nobr></font></td> <td bgcolor="#FFFFFF" valign="bottom"><font face="Tahoma" size="2"><nobr>&82I-B19&VED/JCA</nobr></font></td> <td bgcolor="#FFFFFF" align="right" valign="bottom"><font face="Tahoma" size="2"><nobr>2</nobr></font></td> <td align="right" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td> <td bgcolor="#FFFFFF" valign="bottom"><font face="Tahoma" size="2"><nobr>&82I-B19&VED/JCB</nobr></font></td> <td bgcolor="#FFFFFF" valign="bottom"><font face="Tahoma" size="2"><nobr>BAD</nobr></font></td> </tr> <tr> <td bgcolor="#FFFFFF" align="right" valign="bottom"><font face="Tahoma" size="2"><nobr>96458691</nobr></font></td> <td bgcolor="#FFFFFF" valign="bottom"><font face="Tahoma" size="2"><nobr>US,ULS</nobr></font></td> <td bgcolor="#FFFFFF" valign="bottom"><font face="Tahoma" size="2"><nobr>&VED&82I-JCA</nobr></font></td> <td bgcolor="#FFFFFF" align="right" valign="bottom"><font face="Tahoma" size="2"><nobr>1</nobr></font></td> <td align="right" valign="bottom"><font face="Tahoma" size="2"><nobr></nobr></font></td> <td bgcolor="#FFFFFF" valign="bottom"><font face="Tahoma" size="2"><nobr>&VED&82I-JCA</nobr></font></td> <td bgcolor="#FFFFFF" valign="bottom"><font face="Tahoma" size="2"><nobr>GOOD</nobr></font></td> </tr> </table>


I hope somebody can help me... because i don't know what my problem is
Thank you
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

sunnyland

Well-known Member
Joined
Jan 27, 2006
Messages
912
Hello,

I remember seeing some previous post of yours.

If you feel comfortable with sending me your code. Just send the code relevant to the post that means all code that will give interfere with giving the right or wrong result :include your addBodyRPO("/JCA") and similar if they are used in the function/sub.

Just leave about 4-5 rows of data. Leave 4-5 rows that have some wrong and right result.

Insert a worksheet with the expected right results

Looking at code is always a bit overwhelming. It is easier to provide an answer if we have a sample.

Include all the code that will be running and returning the wrong data as it may be previous code that push wrong result in your cells.

My email is :
mailto:un2trois@tpg.com.au

This is totally up to you of course and your data and code are safe with me.
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
I'd rather like to see your desired results and good explanations.
 

sunnyland

Well-known Member
Joined
Jan 27, 2006
Messages
912
Hie Jindon,

If I managed to get the correct result, I will post the answer.

It is not that I want to keep things private or to myself, it is just that sometimes a picture or A WORKBOOk is better than a lot of explanation.

People work differently. I work better with visual.

Sometimes as well a part of code is not enough to solve a problem and a wider picture [in this case, a workbook] is a better approach.

I didn't mean to offend anyone out there.

Just trying to help.

Have a nice weekend to you all.
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995

ADVERTISEMENT

sunnyland,

I just didn't want to read all the code, that's all.

If the code isn't functioning, it only confuses us...
 

alexoigres

Board Regular
Joined
Dec 2, 2005
Messages
184
Did you get my email???

I hope you have recived my email with the excel file... let me know if not ok... thank you for the help
 

sunnyland

Well-known Member
Joined
Jan 27, 2006
Messages
912
Hello Jindon & Alexoigres

Yes, thank you I have received the file could only look at it recently as I have been busy and didn't have much time to spare.

I didn't achieve 100% of a solution but I notice some problem. First of all it took me a while to understand a little bit more about how you were modifying your data.

From the code I tried to grasp the concept and come out roughly with this understanding:
ChevyPontiacOriginalVersion1.xls
DEFG
17CARModifmodif
18CANADACHEVYCLT&JCA+GMRPO
19CHEVYUS,ULT&JCA+GMRPO
20CHEVYULT&JCA+GMRPO
21CANADAPONTIACPLT&JCB+GMRPO
22CANADACHEVYCLSADDBODYRPO+/JCA
23CHEVYUS,ULSADDBODYRPO+/JCA
24CHEVYULSADDBODYRPO+/JCA
25CHEVYUSADDBODYRPO+/JCA
26CANADAPONTIACPLSADDBODYRPO+/JCB
27CANADACHEVYCLS,CLTNO
28CANADAPONTIACPLS,PLTno
29CHEVYULS,ULT,USno
30CHEVYULS,ULTno
Sheet1

Then there was modifications based on different case of figures:
ChevyPontiacOriginalVersion1.xls
ABCDEF
1CaseCarDivCntrsubLetters
2Case1CANADACHEVY2CC
3Case1CANADAPONTIAC2CgetnorCanadaPontiacRTConversionOptionsP
4Case1USCHEVY1UCMgetnorUsChevyRTConversionOptionsU
5Case2CANADACHEVY1UCMgetnorCanadaChevyRTConversionOptionsC
6Case2USCHEVYDELUCMgetnorUsChevyRTConversionOptionsU
7Case3CANADAPONTIAC2CgetnorCanadaPontiacRTConversionOptionsP
8Case3USCHEVY1UMgetnorUsChevyRTConversionOptionsU
9Case4CANADACHEVY1CMgetnorCanadaChevyRTConversionOptionsC
10Case4CANADAPONTIAC2CgetnorCanadaPontiacRTConversionOptionsP
11Case5CANADAPONTIAC2CgetnorCanadaPontiacRTConversionOptionsP
12Case6USCHEVY1UMgetnorUsChevyRTConversionOptionsU
13Case7CANADACHEVY1CMgetnorCanadaChevyRTConversionOptionsC
Sheet1


What I realised is, it was very easy to loose sight of the data manipulated, and this may affect the final result.

One suggestion, will be to use the temporary variable to hold the initial data, then modify the row directly and use the original variable to add the following lines.

To program well, I suppose you have to understand your subject [not the programming language] very well , so I apologise as I didn't grasp all the car concept.

But I gave it a try.
I think the ordering of the body numbers function is working well, so I least that is something positive at my go at it.


I had a further look at your file, find a work arount to sort your body data, see function trytosort

The problem you are having , I think is, that you are using "one variable" but then you are loosing site of how your data have been modified by your code. The value "TD" is assign via the line series="TD". Meaning further coding will not run as series is now "TD" and not anymore"uls,ult...."

You are using code to modify more than 1 row but you have only one set of variable to hold the data, meaning you are changing the variable and then using the same variable and this is why you don't get the proper result, your code modify the value of gmpro but then expect to add another line so you should temporarely store the unmodified value in a variable:
ie:

If ((InStr(series, "U")<> 0) And (InStr(series, "C")<> 0) And (InStr(series, "P")<> 0)) Then
'GMDAT series applies for US Chevy RT, Canada Chevy RT, and Canada Pontiac RT
tempgpro = gmrpo
'Insert US Chevy RT row
Call getnorUSChevyRTConversionOptions
Call getRTDivCntrSeriesBody("1", "UCM")
Call insertrowtest(6, count)
Call insertFormattedData

'Increase counter
count = count + 1
gmrpo = tempgpro
'Original row is for Canada Pontiac RT
div = "2"
cntr = "C"
Call changeCanadaPontiacYear
Call getnorCanadaPontiacRTConversionOptions

That will not solve all problems but will solve some.


This wasn't obvious, as I don't know anything about car and the logic behind it didn't seems that logical to me may be because I am not familiar to the subject .

Run the main and double check the result you will see that result are closer to expected but the "TD" is not correct as if you change to "TD" straight away than your code cannot run as expected.


One way to sort your body data
48,56,2
will be to use the function trytosort. I am not reinvinting the wheel, I am using the split function that can split a string into an array, in your case using the comma as a separator then I am building a second array that will hold the numeric value, then once again, I am using small function to find out the sorted order then I put back together the array to a string using join function.

Sorry if this is not the perfect solution but this is may be a start.
Rather then modifying a variable, I think a better way will have been to make a copy of the row modify the row than using the copy to insert the following row.

I hope this will bring some light to your problem. Someone else out there may help you out a little more.



Function trytosort(arraystring As String) As String
'function to sort an array of number
'split the string into an array
Dim arri As Variant
arri = Split(arraystring, ",")
Dim arr, x, z
'2 temp arrays are created
'1 with numeral values unsorted
Dim arrnumber() As Integer
ReDim arrnumber(UBound(arri))

'1 array with sorted values
Dim sortedarray() As String
ReDim sortedarray(UBound(arri))

For Each arr In arri
arrnumber(x) = Val(arr)
Debug.Print arrnumber(x)
x = x + 1
Next
x = 0
For x = 1 To UBound(arrnumber) + 1
sortedarray(x - 1) = Str(WorksheetFunction.Small(arrnumber, x))
Next
Dim mysortedarray As String
mysortedarray = VBA.Strings.Join(sortedarray(), ",")
trytosort = mysortedarray
End Function


I hope somehow this was helpfull. I think this is hard to modify someone else code. Everyone will apprehend a problem differently. I will have change data directly into Excel, after inserting a copy of the line.

This is difficult as well, first to grasp the idea behind the code. I think going through your code, I could understand see Sheet1 a little bit more about Chevy Pontiac American Canadian.

I don't think it is a good idea as well to store all model into a cell. But may be you are trying to move up your Excel File into a database file, creating a line for every possible case.


I just hope my effort could pinpoint you in some direction. Programming is a difficult art as everyone is an individual and as such everyone has their own creativity. I am not going to tell you to to go this or that way but remember, it is easy to loose yourself in programming or to loose site of variable.

Keep in mind programming into one special software can be facilitated using what already exists. I you want to modify one line to result in 2 or more lines, you should have 2 or 3 variable sets to hold the value temporaly or first had 2 lines identicals and then work on each one separatly.

Hope this is all usefull somehow.
 
Status
Not open for further replies.

Watch MrExcel Video

Forum statistics

Threads
1,113,955
Messages
5,545,162
Members
410,667
Latest member
Gaexel
Top