0

(Updated) I'm new to VBA and have been trying various things for hours but I can't seem to figure out how to simply multiply a decimal user input of 1.1 by the cell value of C1 (71388.92). Here is what I've tried last:

Private Sub CommandButton1_Click()
Dim myValue As Variant

myValue = InputBox("Prompt", "Title")
Range("C5").Value = myValue * Range("C1")

End Sub

I get a runtime error '13'- Type mismatch perhaps because cell C1's value is derived from a json web query. I've tried Variant, Double, Single, Long and Data Types Decimal, Number and Currency.

Download link: https://www.dropbox.com/sh/tg5f2a71k9jy6xq/AACrhI9_6VG8vJkDr0QVCbSUa?dl=0

0m3r
  • 12,286
  • 15
  • 35
  • 71
kymadic
  • 1
  • 5
  • You should watch this series: [Excel VBA Introduction Part 1 - Getting Started in the VB Editor](https://www.youtube.com//watch?v=KHO5NIcZAc4&index=1&list=PLNIs-AWhQzckr8Dgmgb3akx_gFMnpxTN5). – TinMan Nov 19 '18 at 23:51
  • Your code is valid. What is the problem? – TinMan Nov 19 '18 at 23:52
  • I think you need an error check on the value entered by the user. What if it's alphanumeric? or nothing? – cybernetic.nomad Nov 19 '18 at 23:58
  • I get a Runtime error '13'. Type mismatch perhaps because cell C1's value is derived from a Json web query. I've edited my question to include the query code. – kymadic Nov 20 '18 at 00:13
  • 2
    It shouldn't matter the source of where `C1` obtained it's value - what matters is the actual value of `C1`. That's the question we need answered. – K.Dᴀᴠɪs Nov 20 '18 at 00:14
  • This really doesn't make any sense. This code should work as long as `Range("C1")` and `myValue` contain just numbers even if they are stored as text. Can you provide a download link? – TinMan Nov 20 '18 at 00:22
  • Also, why would you use `myValue As Variant` when you know that the value will be numerical? Perhaps `Double` would be a better data type? – K.Dᴀᴠɪs Nov 20 '18 at 00:58
  • In the VBA editor, press Ctrl+G to get to the Immediate Window, and type "?TypeName(Range("C1").Value)" (without the outermost quotes). I bet the result is String. – Excelosaurus Nov 20 '18 at 02:01
  • Now using a different api url I got it to work but not with decimals which I need. I have updated the download link. Tried Double, Single, Variant and Decimal, Number and Currency but none work with decimals. Input 1.1. – kymadic Nov 20 '18 at 02:35
  • I tested with the file in the main post, changed the api into "title" and everything worked fine, no matter 10 or 1.1. Can you describe your problem again? Also, as there are multiple download links for the same file, please delete old ones so that only newest one remains. – Mr.K Nov 20 '18 at 03:15
  • Google drive converts .xlsm to .xlsx so I've uploaded to dropbox https://www.dropbox.com/sh/tg5f2a71k9jy6xq/AACrhI9_6VG8vJkDr0QVCbSUa?dl=0 – kymadic Nov 20 '18 at 03:50
  • Can I get some upvotes for posting the correct solution to my own question? :) – kymadic Dec 11 '18 at 23:27

3 Answers3

0

I think this is your solution:

myValue = InputBox("Prompt", "Title")
Range("C5").Select
ActiveCell.FormulaR1C1 = myValue * Range("C1")
ali
  • 23
  • 7
  • @kymadic - "it doesn't work" is not a helpful response! – SJR Nov 20 '18 at 08:49
  • but i tested this in excel 2016 and it works. can you explain your problem? debug your code step by step (F8) and check variable content. – ali Nov 20 '18 at 12:15
  • I tried your code in the "new api version.xlsm" and I still get the same runtime error '13': Type mismatch on the line which contains ActiveCell.FormulaR1C1 = myValue * Range("C1"). The input and output should contain a decimal number. It works with whole numbers but not decimals which are required up to 6 or so decimal points. It doesn't work at all in the previous "preferred version.xlsm". – kymadic Nov 20 '18 at 17:58
0

Declare as double instead of variant

Dim myValue As Double
myValue = InputBox("Prompt", "Title")
Range("C5").Value = myValue * Range("C1")
Manoj Babu
  • 31
  • 2
  • I've tried Double, Single, Long and Variant. None work and I need to enter numbers to the effect of 0.0012345 – kymadic Nov 20 '18 at 18:00
0

Found Solution:

The error with the InputBox accepting a decimal entry lies with the "Thousands Separator" settings and whether you enter a comma or point into the InputBox. I had my "Thousands Separator" set to use a point . because I'm working with .csv file exports so it was useful. Either will work but you have to change your Windows decimal symbol settings by going to:

Control Panel > Language > Change date, time, or number formats > Additional Settings > Decimal symbol.

As for the multiplication part: The original InputBox returns a string. Using Val() converts the number string to a number and is the correct VBA script:

Private Sub CommandButton1_Click()
Dim myValue As Variant

myValue = Val(InputBox("Prompt", "Title"))
Range("C5").Value = myValue * Range("C3")

End Sub
kymadic
  • 1
  • 5