Summing variables from an input box

[willh123]

Hi All,

Please could someone help with an issue I'm having?

I've started making a macro which a user enters a string of letters for example CCGTXYZA, each letter represents a value which is given in the spreadsheet. I would like to sum the total and msg box the result. How can this be done please?

Thanks

Will

 

[Fishboy]

Hi All,

Please could someone help with an issue I'm having?

I've started making a macro which a user enters a string of letters for example CCGTXYZA, each letter represents a value which is given in the spreadsheet. I would like to sum the total and msg box the result. How can this be done please?

Thanks

Will

Hi Will, welcome to the boards.

You will need to explain the logic behind how the string of letters relates to / represents the spreadsheet values. We can probably then guide you on how to sum these values.

 

[willh123]

Hi

Thanks for the reply. CGAT are actually constants around 300 XYZ are variables the value of which are given in the spreadsheet. For example X in one column, the variables name in the next column and the value in the column after:

C = 100.21, G = 563.25, A = 205.31, T = 363.73

X "Name1" 273.71
Y "Name2" 373.25
Z "Name3" 207.85

When the user enters the string CAXYZ into the input box I would like it to sum:

100.21 + 205.31 + 273.71 + 373.25 + 207.85

Thanks

Will

 

[Fishboy]

Hi

Thanks for the reply. CGAT are actually constants around 300 XYZ are variables the value of which are given in the spreadsheet. For example X in one column, the variables name in the next column and the value in the column after:

C = 100.21, G = 563.25, A = 205.31, T = 363.73

X "Name1" 273.71
Y "Name2" 373.25
Z "Name3" 207.85

When the user enters the string CAXYZ into the input box I would like it to sum:

100.21 + 205.31 + 273.71 + 373.25 + 207.85

Thanks

Will

Hi Will,

I am a little confused...

Can you share some example data? Check the link my signature for the MrExcel HTML Maker so you can post some.

 

[willh123]

Hi

If C = 10, G = 5, A =7 & T = 8.

If I then put CGAT into an input box how do I make it sum: C + G + A + T (10 + 5 + 7 +8)?

Thanks

Will

 

[Fishboy]

Hi again Will,

I understand the concept of what you are describing, it is not that which is confusing me. I realise that each letter represents a numeric value and that you want to sum them, what I am confused about is how Excel is supposed to know that C = 10, and that G = 5 and so on.

Unless you have a table somewhere where the corresponding values are laid out for us to use a a lookup, I do not see how Excel is supposed to know what numeric values need summing.

 

[willh123]

Hi

Sub sequencemass()

Dim C As Single
Dim G As Single
Dim T As Single
Dim A As Single
Dim X As Single
Dim Y As Single
Dim Z As Single
Dim sequence As String
Dim Mass As Single


Sheets("Sheet1").Activate

A = 251.24
G = 267.24
C = 227.22
T = 242.23
X = Range("C8").Value
Y = Range("C9").Value
Z = Range("C10").Value

sequence = InputBox("Enter Sequence ")

Mass = Sum(sequence)

MsgBox Mass

End Sub

 

[Fishboy]

Try this:

Sub sequencemass()


Dim C As Single
Dim G As Single
Dim T As Single
Dim A As Single
Dim X As Single
Dim Y As Single
Dim Z As Single
Dim sequence As String
Dim Mass As Single
Dim lLoop As Long
Dim lCount As Long


Sheets("Sheet1").Activate


sequence = InputBox("Enter Sequence ")
     
    lCount = Len(sequence)
    ReDim strArray(lCount - 1)
     
    For lLoop = 0 To lCount - 1
        Select Case UCase(Mid(sequence, lLoop + 1, 1))
            Case "A"
                Mass = Mass + 251.24
            Case "G"
                Mass = Mass + 267.24
            Case "C"
                Mass = Mass + 227.22
            Case "T"
                Mass = Mass + 242.23
            Case "X"
                Mass = Mass + Range("C8").Value
            Case "Y"
                Mass = Mass + Range("C9").Value
            Case "Z"
                Mass = Mass + Range("C10").Value
        End Select
    Next lLoop


MsgBox Mass


End Sub

 

[willh123]

Thanks Fishboy, works perfectly. nothing fishy about your code...

 

[Fishboy]

Thanks Fishboy, works perfectly. nothing fishy about your code...

Happy to help mate. Glad to hear you got it working as required.

This was actually quite a fun one to try and find a solution to!