PDA

View Full Version : input box to define formulas contraints



SteveM99
02-21-2018, 03:41 PM
I have a file which needs to lookup data in other file and capture it.
I want to use the Index Match formula to look at a range in the second file and match it against the first file (and bring the result back).
Trying to use an input box to get the three pieces needed for the formula. The formula is Index(Range1,Match(Cell1,Range2),0)).
I need the input box to allow me to point and click to the two ranges and one cell to form a single formula(which I can them copy/change/evaluate, etc).
I would like the ranges and cell referenced to be pasted into another sheet of the first file (call it the input box file) where I can take next step in programing.
Was having a problem with getting the 1st range selected and copying that selection onto a sheet. I wanted the range as defined not the actual range, i.e. "A5:A10" of File X instead of the range contents of A5:A10 which would be a bunch of rows of File X.
Got frustrated with just that simple task of getting the range text ("A5:A10") from an input box I figured I would just define the whole effort here.

SteveM99
02-22-2018, 06:54 AM
Forum viewers thank you for reviewing this post. I took a break from my code and then came back fresh and figured out what I was doing wrong. I needed to use Range.Address. Below is my code which works exactly for what I needed.


Sub Test101()

Dim ThisRng As Excel.Range

Set ThisRng = Application.InputBox("Select Index range", "Get Range", Type:=8)



Dim ThisRng2 As Excel.Range
Set ThisRng2 = Application.InputBox("Select Match Cell", "Get Cell", Type:=8)

'Example to put into cell ''Sheets(1).Range("a5").Value = ThisRng2.Address


Dim ThisRng3 As Excel.Range
Set ThisRng3 = Application.InputBox("Select Match Range", "Get Range", Type:=8)



Dim ThisRng4 As Excel.Range
Set ThisRng4 = Application.InputBox("Select Formula Cell", "Select Cell", Type:=8)

ThisRng4.Formula = "=index(" & ThisRng.Address & ",Match(" & ThisRng2.Address(False, False) & "," & ThisRng3.Address & ",0))"


End Sub