VBA to set Active Cell in a Range based on value in a single cell.

[jasonfuchs70]

Thanks to all who will participate, I am a new poster here, so apologize if I'm not following protocal. What I have is a table of data with 3 columns. The first column represents "yardage" in increments of 25 yards. The next 2 columns represent ballistic performance characteristics which are calculated based upon the "yardage" value in column A. At the same time, I have cell F1 which I input the required yardage number to calculate on. This number (in F1 only appears once in the Column A "yardage" range. What I am trying to do is the following:
1)enter value in cell F1 (number shall be in increments of 25 yards)
2)when user enters value of "150" in "F1", VBA code will search "Yardage" column to find cell with "150" in it.
3)Cell found with the value of "150" in the yardage column will be made the "Active Cell" in the worksheet.

4)once set as active cell, offset 2 rows over, and this will be the cell utilized in "solver" to set as zero

I have everything working with VBA currently, but am using "if statements" in VBA to loop thru solver code set for increments of 100 yards. So each time I enter a value in F1, it must be in an increment of 100, and the time it takes to go thru the loop has become overwhelming. I think the above approach, will be more efficient, and user friendly. Ultimately will build a drop down box with yardage values in 25 yd. increments the user can only select from.

thanks

A1	B1	C1	D1	E1	F1	G1	H1
Yardage	Velocity	Drop			150	Input Desired Yardage
0
25
50
100
125
150
175
200

<tbody>
</tbody>

 

[dchaney]

Can you post the code you use? I am sure someone here could help then.

 

[jasonfuchs70]

This is what I currently have.

Sub FindValue()


' Finds value entered into "INPUT BOX" and sets as active cell.




Dim FindString As String
Dim Rng As Range
FindString = InputBox("Enter ZERO Range Distance")
If Trim(FindString) <> "" Then
With Sheets("G7 Trajectory").Range("A:A")
Set Rng = .Find(What:=FindString, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Rng Is Nothing Then
Application.Goto Rng, True
Else
MsgBox "Cannot Calculate This ZERO Range"
End If
End With
End If
' Selects and activates cell to right of X value (Range) in ballistics program
ActiveCell.Offset(0, 2).Select


' Solves and Calculates Ballistics based on above Inputed ZERO Range
SolverReset
SolverOk SetCell:=ActiveCell.Address, MaxMinVal:=3, ValueOf:=0, ByChange:="$l$9", Engine:= _
1, EngineDesc:="GRG Nonlinear"
SolverSolve userfinish:=True
Range("a1").Select

this allows me to input the value noted above in cell F1 into a message box, and it works fine. However, I don't like this approach because it does not store the "F1" value anywhere else. And I need this value as part of the display results to the overall data output. If I could use the code above, but take out the "entering of the value into a message box" and instead enter it in cell F1 instead, I think I would be satisfied.

 

[dchaney]

If you are just looking to place the value from the Input Box into your Spreadsheet in Cell F1 just add the following

[FONT=arial]Sheets("G7 Trajectory").[/FONT]Range("F1") = [FONT=arial]FindString[/FONT]

or you can do this and get rid of the Input Box

Replace

[FONT=arial]FindString = InputBox("Enter ZERO Range Distance")[/FONT]

With

[FONT=arial]FindString = [/FONT][FONT=arial]Sheets("G7 Trajectory").[/FONT]Range("F1").Value

 

[jasonfuchs70]

Works like a champ. One other thing if you could help me. When the code above runs, and it locates the inputted value in the range under the yardage column (column A) the entire spreadsheet shifts down only showing those values below (under) the inputted value. Is there a way to stop this by removing or modifying a portion of the code above.

Thanks

 

[dchaney]

Try This,

Dim x as String
Dim Rng As Range
Dim FindString As String

FindString = Sheets("G7 Trajectory").Range("F1").Value

    If Trim(FindString) <> "" Then

        With Sheets("G7 Trajectory").Range("A:A")
            Set Rng = .Find(What:=FindString, _
            After:=.Cells(.Cells.Count), _
            LookIn:=xlValues, _
            LookAt:=xlWhole, _
            SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, _
            MatchCase:=False)
        End With
        
        If Rng Is Nothing Then MsgBox "Cannot Calculate This ZERO Range"
                  
    End If

x = Range("A" & Rng.Row).Offset(0, 2).Address

' Solves and Calculates Ballistics based on above Inputed ZERO Range
SolverReset

SolverOk SetCell:=x, MaxMinVal:=3, ValueOf:=0, ByChange:="$l$9", Engine:=1, EngineDesc:="GRG Nonlinear"

SolverSolve userfinish:=True

Hard to test this as I do not have the SolverReset and other codes

 

[jasonfuchs70]

Thanks alot. Im new to VBA, and not well versed at all on coding, only what I can pick up on Google, and places like Mr. Excel. But after seeing your edits, I have gained a better understanding of how this code works, and can use in future. I appreciate your efforts and help.

Thanks

 

[dchaney]

My pleasure, that is the way I am learning... Also one thing helping me out is actually helping others on here, I am learning more ways to do stuff.