InStr ... If and Then

alexoigres · Oct 11, 2006

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"></th> <th align="right" valign="bottom"></th> <th valign="bottom">MY MACRO</th> <th align="right" valign="bottom"></th> <th align="right" valign="bottom"></th> <th valign="bottom">I SHOULD GET</th> <th align="right" valign="bottom"></th> </tr> <tr> <td bgcolor="#FFFF00" align="right" valign="bottom">96464829</td> <td bgcolor="#FFFF00" valign="bottom">ULT,PLS,PLT</td> <td bgcolor="#FFFF00" valign="bottom">&JCA&L91&ML4&B19&GH4&D8R&BMF</td> <td bgcolor="#FFFF00" align="right" valign="bottom">1</td> <td align="right" valign="bottom"></td> <td bgcolor="#FFFF00" valign="bottom">&JCA&L91&ML4&B19&GH4&D8R&BMF</td> <td bgcolor="#FFFF00" valign="bottom">GOOD</td> </tr> <tr> <td bgcolor="#FFFFFF" align="right" valign="bottom">96464829</td> <td bgcolor="#FFFFFF" valign="bottom">ULT,PLS,PLT</td> <td bgcolor="#FFFFFF" valign="bottom">&JCA&L91&ML4&B19&GH4&D8R&BMF</td> <td bgcolor="#FFFFFF" align="right" valign="bottom">2</td> <td align="right" valign="bottom"></td> <td bgcolor="#FFFFFF" valign="bottom">&L91&ML4&B19&GH4&D8R&BMF</td> <td bgcolor="#FFFFFF" valign="bottom">BAD (no JCA)</td> </tr> <tr> <td bgcolor="#FFFFFF" align="right" valign="bottom">96832759</td> <td bgcolor="#FFFFFF" valign="bottom">PLS,PLT</td> <td bgcolor="#FFFFFF" valign="bottom">&L91&ML4&B19&GH4&D8R&BMF</td> <td bgcolor="#FFFFFF" align="right" valign="bottom">2</td> <td align="right" valign="bottom"></td> <td bgcolor="#FFFFFF" valign="bottom">&L91&ML4&B19&GH4&D8R&BMF</td> <td bgcolor="#FFFFFF" valign="bottom">GOOD</td> </tr> <tr> <td bgcolor="#FFFF00" align="right" valign="bottom">96537407</td> <td bgcolor="#FFFF00" valign="bottom">CLS,PLS</td> <td bgcolor="#FFFF00" valign="bottom">&82I-B19&VED/JCA</td> <td bgcolor="#FFFF00" align="right" valign="bottom">1</td> <td align="right" valign="bottom"></td> <td bgcolor="#FFFF00" valign="bottom">&82I-B19&VED/JCA</td> <td bgcolor="#FFFF00" valign="bottom">GOOD</td> </tr> <tr> <td bgcolor="#FFFFFF" align="right" valign="bottom">96537407</td> <td bgcolor="#FFFFFF" valign="bottom">CLS,PLS</td> <td bgcolor="#FFFFFF" valign="bottom">&82I-B19&VED/JCA</td> <td bgcolor="#FFFFFF" align="right" valign="bottom">2</td> <td align="right" valign="bottom"></td> <td bgcolor="#FFFFFF" valign="bottom">&82I-B19&VED/JCB</td> <td bgcolor="#FFFFFF" valign="bottom">BAD</td> </tr> <tr> <td bgcolor="#FFFFFF" align="right" valign="bottom">96458691</td> <td bgcolor="#FFFFFF" valign="bottom">US,ULS</td> <td bgcolor="#FFFFFF" valign="bottom">&VED&82I-JCA</td> <td bgcolor="#FFFFFF" align="right" valign="bottom">1</td> <td align="right" valign="bottom"></td> <td bgcolor="#FFFFFF" valign="bottom">&VED&82I-JCA</td> <td bgcolor="#FFFFFF" valign="bottom">GOOD</td> </tr> </table>

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

sunnyland · Oct 13, 2006

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 · Oct 13, 2006

I'd rather like to see your desired results and good explanations.

sunnyland · Oct 14, 2006

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 · Oct 14, 2006

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 · Oct 16, 2006

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 · Oct 19, 2006

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

D

E

F

G

17

CAR

Modif

modif

18

CANADACHEVY

CLT

&JCA+GMRPO

19

CHEVY

US,ULT

&JCA+GMRPO

20

CHEVY

ULT

&JCA+GMRPO

21

CANADAPONTIAC

PLT

&JCB+GMRPO

22

CANADACHEVY

CLS

ADDBODYRPO+/JCA

23

CHEVY

US,ULS

ADDBODYRPO+/JCA

24

CHEVY

ULS

ADDBODYRPO+/JCA

25

CHEVY

US

ADDBODYRPO+/JCA

26

CANADAPONTIAC

PLS

ADDBODYRPO+/JCB

27

CANADACHEVY

CLS,CLT

NO

28

CANADAPONTIAC

PLS,PLT

no

29

CHEVY

ULS,ULT,US

no

30

CHEVY

ULS,ULT

no

Sheet1

Then there was modifications based on different case of figures:

ChevyPontiacOriginalVersion1.xls

A

B

C

D

E

F

1

Case

Car

Div

Cntr

sub

Letters

2

Case1

CANADACHEVY

2

C

3

Case1

CANADAPONTIAC

2

C

getnorCanadaPontiacRTConversionOptions

P

4

Case1

USCHEVY

1

UCM

getnorUsChevyRTConversionOptions

U

5

Case2

CANADACHEVY

1

UCM

getnorCanadaChevyRTConversionOptions

C

6

Case2

USCHEVY

DEL

UCM

getnorUsChevyRTConversionOptions

U

7

Case3

CANADAPONTIAC

2

C

getnorCanadaPontiacRTConversionOptions

P

8

Case3

USCHEVY

1

UM

getnorUsChevyRTConversionOptions

U

9

Case4

CANADACHEVY

1

CM

getnorCanadaChevyRTConversionOptions

C

10

Case4

CANADAPONTIAC

2

C

getnorCanadaPontiacRTConversionOptions

P

11

Case5

CANADAPONTIAC

2

C

getnorCanadaPontiacRTConversionOptions

P

12

Case6

USCHEVY

1

UM

getnorUsChevyRTConversionOptions

U

13

Case7

CANADACHEVY

1

CM

getnorCanadaChevyRTConversionOptions

C

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.

Von Pookie · Nov 15, 2006

Continued here:
http://www.mrexcel.com/board2/viewtopic.php?t=243325

InStr ... If and Then

alexoigres

Board Regular

Excel Facts

sunnyland

Well-known Member

jindon

MrExcel MVP

sunnyland

Well-known Member

jindon

MrExcel MVP

alexoigres

Board Regular

sunnyland

Well-known Member

Von Pookie

MrExcel MVP

Similar threads

Forum statistics

Share this page

InStr ... If and Then

Board Regular

Excel Facts

Well-known Member

MrExcel MVP

Well-known Member

MrExcel MVP

Board Regular

Well-known Member

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock