Excel vba help

[wahmed]

Hello All.

I do have worksheet where I have some date like this
S/10 M/10 L/10 XL/10 XXL/10
apparently these are sizes with quantity mentioned but all this is in one string or in one cell.

So How can I have total of quantities mentioned after "/" slash ?

like I wish to have total of 50 in front of that cell, quantities may be vary cell to cell.

Your help in this regard would be highly appreciated.

regards

Wahmed

 

[wigi]

Hi

You could use this User Defined Function:

Function SumAfterSlash(s As String)
    For i = 0 To UBound(Split(s, "/"))
        SumAfterSlash = SumAfterSlash + Val(Split(s, "/")(i))
    Next
End Function

Among the advantages of this approach: no helper columns and no array formula. But it's VBA, it's not a native Excel function.

Wigi

 

[wahmed]

Dear Wigi

thanks for your reply and solution, its working perfectly, thank you very much, but would you be so kind to explain how to use ubound please ?

best regards

wahmed

 

[wigi]

Split will split the string into an array, the split happens each time the character / is found in the string. The result is an array. The For Next loop from 0 to UBound loops through the elements in the array, UBound is used to determine the size of the array.

If you select Ubound in VBE and hit F1, you will read that:

UBound: Returns a Long containing the largest available subscript for the indicated dimension of an array.

Wigi

 

[wahmed]

thanks wigi.

your help is appreciated.

best regards

wahmed

 

[wahmed]

Hello.

Is there any way I can sum value of cell in next cell with simple excel formulas ?

Like if I have value 10 10 10 10 in Cell A1 then how could I have sum of this which is 40 in Cell A2 ?

best regards

Wahmed

 

[wigi]

Yes you could in theory and with VBA-code, but why not using 4 different cells? You're overcomplicating here. Did you know how many different cells each and every Excel sheet has?

Please also pay attention to the forum rules:
- 1 topic = 1 question, so you would need to create a new topic with your last question
- a descriptive topic title. "Excel vba help" is one of the worst topic titles you can pick (the Search function of the forum cannot handle it).

Wigi

 

[Jon von der Heyden]

Please also pay attention to the forum rules:
- 1 topic = 1 question, so you would need to create a new topic with your last question
- a descriptive topic title. "Excel vba help" is one of the worst topic titles you can pick (the Search function of the forum cannot handle it).
Wigi

I don't agree with this. I see this question a perfectly acceptable continuation of the first question. Please continue with this thread.

 

[wigi]

Ok then.