View Full Version : How do I change ?i? as an integer (ie 1,2,3,4?) to ?i? (1-10) with intervals of 0.1?

01-20-2009, 07:45 AM
In cell F7 i have the formula '=E7/(E7+C7)*100'

Im trying to set up a code to goal seek:

set cell: F7
to value: i as an integer 0.1 to 15 (with 0.1 intervals)
By Changing Cell: E7

I have got it partially working (see below) though it is messy and I don’t know how to get i with different intervals than 1?

Sub test()
Dim ws1 As Worksheet, ws2 As Worksheet, i As Integer, OldVal, Header
Header = Array("F7", "E7")
Set ws1 = ActiveSheet
Sheets.Add after:=Sheets(Worksheets.Count)
Set ws2 = ActiveSheet
ws2.Range("A1:C1").Value = Header
For i = 1 To 5
ws2.Range("A" & i + 1).Value = i
ws2.Range("B" & i + 1).Value = ws1.Range("F7").Value
OldVal = ws1.Range("E7").Value
ws1.Range("F7").GoalSeek Goal:=i, ChangingCell:=ws1.Range("E7")
ws2.Range("C" & i + 1).Value = ws1.Range("E7").Value
ws1.Range("E7").Value = OldVal
Next i

End Sub

01-20-2009, 07:52 AM
Welcome to the forum- it's always good to see new members.

Integers are whole value numbers- 1,2,3,etc. Integers do not show decimal values, so you are going to have to use a different data type. I'd suggest using Double.

For more information on data types, open up Help in the VBIDE and search for Data Type.