Simplifying a Formula

[Tootlez]

My formula is super long and I know there has to be a way to make it shorter, probably with a VBA loop, but I'm sick of looking at it, and I don't know where to start.

General Setup:
Sheet 1 has 3 cells: Cell B has a VLookUp to Sheet 2, which returns a number. Cell C uses the number to print Cell A as a image file name with Cell B's value minus one to create a image gallery list.

[TABLE="class: grid, width: 800"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD]XX-SKU[/TD]
[TD]5[/TD]
[TD]/XX-SKU_1.jpg,/XX-SKU_2.jpg,/XX-SKU_3.jpg,/XX-SKU_4.jpg[/TD]
[/TR]
</tbody>[/TABLE]

The Formula in Cell C is ridiculously long due to the amount of IF's needed to cover at least to 20 cases of numbers.

I'm not all too familiar with all the syntax/referencing for VBA. I am not sure how to generate the list properly either.

The first number (not shown in the table above) has NO underscore, and is not apart of this Cell C. Cell C is blank if there is only a 1 image.
For the following gallery items: there is to be a comma between each, and all start with the forward slash.

Any help is much appreciated

 

[Tootlez]

never mind ... .....

 

[Tootlez]

Getting an error about the fname(i) not being defined.

 

[István Hirsch]

Then try this:

Function fffx(s, n As String) As String
For i = 1 To n - 1
        fffx = fffx & "/" & s & "_" & i & ".jpeg,"
    Next
    fffx = Left(fffx, Len(fffx) - 1)
End Function

 

[Tootlez]

i knew what you were after but with that extra function in there I got lost.

That works awesome Thanks.

Curious how can you handle a "replace" in there. I have a few skus that have characters that cannot be in an image name " /2" (that's a space with a forward slash)
I'm thinking that I could just put in the Replace(s, " /2", "") but I wanted to double check before I crash it.

 

[István Hirsch]

i knew what you were after but with that extra function in there I got lost.

That works awesome Thanks.

Curious how can you handle a "replace" in there. I have a few skus that have characters that cannot be in an image name " /2" (that's a space with a forward slash)
I'm thinking that I could just put in the Replace(s, " /2", "") but I wanted to double check before I crash it.

You are welcome.

Yes, it seems good if you put this line s=Replace(s," /2","") above the loop, to be the second line of the code.

 

[Tootlez]

Awesome!

I'm learning slowly, but glad to know I got that right (even though its not that far off of the substitute formula)

Thanks again!

 

[Tootlez]

Wait ... I ran into a problem.
I think I know how to fix it, but I'm not sure where to put it exactly.

if the value of 'n' is 1, the cell needs to be blank, I get an error if I have 1.
Otherwise it works great.

 

[István Hirsch]

Try this:

Function fffx(s, n As String) As String
  If n > 1 Then
    s = Replace(s, " /", "")
    For i = 1 To n - 1
        fffx = fffx & "/" & s & "_" & i & ".jpeg,"
    Next
    fffx = Left(fffx, Len(fffx) - 1)
  End If
End Function

 

[Tootlez]

Thanks Istvan, that does the trick. I was thinking that would be a way to fix it, but I was trying was an = but the great than makes more sense.

And I think I found why none of my attempts were working --- I forgot the "End If"

So how does it know not to put a value when the n is 1? Most of the coding I know needs to tell it to put a value, even if it's blank.

 

[István Hirsch]

Thanks Istvan, that does the trick. I was thinking that would be a way to fix it, but I was trying was an = but the great than makes more sense.

And I think I found why none of my attempts were working --- I forgot the "End If"

So how does it know not to put a value when the n is 1? Most of the coding I know needs to tell it to put a value, even if it's blank.

"If n" separates the routes: if n > 1 each line under the "if" line will be processed, if n <= 1 the program jumps to "End If" and does nothing.