another macro questions

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Res = InputBox("What is the cell address?")
If StrPtr(Res) = 0 Then
MsgBox "User Clicked Cancel"
ElseIf Res = vbNullString Then
MsgBox "User Clicked OK with no input"
Else
MsgBox "User Entered cell " & Res
End If
'
Application.Goto Reference:="target_cusip"
ActiveCell.FormulaR1C1 = Res

Application.Run "RefireBLP"
ActiveWorkbook.Names.Add Name:="target_cusip"
RefersToR1C1: x_ "=summary!+ " & rng.Address(ReferenceStyle:=xlR1C1)
Application.Run "RefireBLP"

I think I have looked at this too much. What I need for it to do is bring
up a box asking what cell address I want to use. Then I need to verify the
address to be used. I next want to name the cell target_cusip so that I can
use it in a larger formula. Why? Because sometimes the taget_cusp is M107
and sometimes it is M108, or M109....see why I would like to make it a little
easier? Thanks for any and all suggestions!
 
I don't know if this helps any. I added a little to what you have.

Res = InputBox("What is the cell address?")
Do
If StrPtr(Res) = 0 Then
MsgBox "User Clicked Cancel"
Exit Sub
ElseIf Res = vbNullString Then
MsgBox "User Clicked OK with no input"
Else
MsgBox "User Entered cell " & Res
End If
Loop Until Res > 0
Application.Goto Reference:="target_cusip"
ActiveCell.FormulaR1C1 = Res

Application.Run "RefireBLP"
ActiveWorkbook.Names.Add Name:="target_cusip"
'RefersToR1C1: x_ "=summary!+ " & rng.Address(ReferenceStyle:=xlR1C1)
Application.Run "RefireBLP"
 
Well he does look better, but it still isn't doing the last half that I need
it to do. I think my brain has left me....the bad part is I still love this
stuff.
 
Do you have a cell reference set up? I am assuming you have that.

The line "Refers to..." I assumed was an explanatory note and not actually
part of the procedure.

Can you tell me more about "RefireBLP"?
 
It seems you are trying to move the name target_cusip around. Wouldn't
it be easier to leave its address fixed and copy the cusip (indicated
by the InputBox) to it?

Merjet
 
If you need to setup a Reference, Highlight the cell or group of cells that
you want to name as the Reference. In the box just above Column A you will
see the name of the first cell highlighted in the group. Click in that box
and type "target_cusip" (without quotes). The macro will then assign the
cell address entered in the Input Box to that cell.
 
sure
refireBLP is something that will relink (or rather update) the informaiton
to a stock market site.
No what the main issue is that I have been working 11 hour days for the last
two months and some of this is starting to blur before me. One more formula
tho and it will be complete and well worth it all. This is just a small
chunk of it. I need to move the referance cell around and since it is a
named cell that is used in other formulas I can't just say M111, it has to be
M111 is now target_cusp
 
I think it might, but I would need to see it to understand what you are
saying...forgive me for being dense but I think I can only see what's in my
mind. Maybe I am looking at it wrong. It's only going to move around 10
cells all in column M all one nect to the other..hmmm. Mabye.
 
I added a line:
ActiveWorkbook.Names.Add Name:="target_cusip", RefersToR1C1:=Range(Res)
to the code below. This applies the named range "target_cusip" to the cell
entered in the input box.

Res = InputBox("What is the cell address?")
Do
If StrPtr(Res) = 0 Then
MsgBox "User Clicked Cancel"
Exit Sub
ElseIf Res = vbNullString Then
MsgBox "User Clicked OK with no input"
Else
MsgBox "User Entered cell " & Res
End If
Loop Until Res > 0
ActiveWorkbook.Names.Add Name:="target_cusip", RefersToR1C1:=Range(Res)
Application.Goto Reference:="target_cusip"
ActiveCell.FormulaR1C1 = Res

Application.Run "RefireBLP"
ActiveWorkbook.Names.Add Name:="target_cusip"
'RefersToR1C1: x_ "=summary!+ " & rng.Address(ReferenceStyle:=xlR1C1)
Application.Run "RefireBLP"
 
I added a line:
ActiveWorkbook.Names.Add Name:="target_cusip", RefersToR1C1:=Range(Res)
to the code below. This applies the named range "target_cusip" to the cell
entered in the input box.

Res = InputBox("What is the cell address?")
Do
If StrPtr(Res) = 0 Then
MsgBox "User Clicked Cancel"
Exit Sub
ElseIf Res = vbNullString Then
MsgBox "User Clicked OK with no input"
Else
MsgBox "User Entered cell " & Res
End If
Loop Until Res > 0
ActiveWorkbook.Names.Add Name:="target_cusip", RefersToR1C1:=Range(Res)
Application.Goto Reference:="target_cusip"
ActiveCell.FormulaR1C1 = Res

Application.Run "RefireBLP"
ActiveWorkbook.Names.Add Name:="target_cusip"
'RefersToR1C1: x_ "=summary!+ " & rng.Address(ReferenceStyle:=xlR1C1)
Application.Run "RefireBLP"
 
Hi Jim. I think we are getting closer. What it keeps doing is typing the
cell address in the chosen cell now. and it isn't naming it target_cusp, but
if I ask it to go to the target_cusp it will go to that cell.

Res = InputBox("What is the cell address?")
Do
If StrPtr(Res) = 0 Then
MsgBox "User Clicked Cancel"
Exit Sub
ElseIf Res = vbNullString Then
MsgBox "User Clicked OK with no input"
Else
MsgBox "User Entered cell " & Res
End If
Loop Until Res > 0
ActiveWorkbook.Names.Add Name:="target_cusip", RefersToR1C1:=Range(Res)
Application.Goto Reference:="target_cusip"
ActiveCell.FormulaR1C1 = Res

Application.Run "RefireBLP"
ActiveWorkbook.Names.Add Name:="target_cusip"
Application.Run "RefireBLP"

I know it has to do with something I either forgot and have entered wrong,
I'm just not sure what or where....
 
nope won't work. Each cell has a formula in that imports information about
that stock and the main formaula needs to be able to call for target_cusp to
run information against the different stock that the cell is representing.
confusing yes?
 
I did figure out what I was doing wrong with typing in the cell. Now to get
the darn thing to say what it is named :-)

It still isn't naming it target_cusp, but
if I ask it to go to the target_cusp it will go to that cell.

Res = InputBox("What is the cell address?")
Do
If StrPtr(Res) = 0 Then
MsgBox "User Clicked Cancel"
Exit Sub
ElseIf Res = vbNullString Then
MsgBox "User Clicked OK with no input"
Else
MsgBox "User Entered cell " & Res
End If
Loop Until Res > 0
ActiveWorkbook.Names.Add Name:="target_cusip", RefersToR1C1:=Range(Res)
Application.Goto Reference:="target_cusip"
ActiveCell.FormulaR1C1 = Res

I know it has to do with something I either forgot and have entered wrong,
 
Change this line
ActiveCell.FormulaR1C1 = Res
to
ActiveCell.FormulaR1C1 = "target_cusip"
 
I used activecell.name instead of formula and now it is working perfectly!
Thandks for all your time and help in this Jim!
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How do I make the formula see the message box answer? 4
marco issues 1
MsgBox "Choose one ", vbYesNoCancel, " Three Options. " 3
Cancel if the first one is found 4
Why does this not stop when I push "yes' or "cancel in the message 14
Help On search macro 5
Was ok in 2003 not in 2007 1
I would like to create a button on the worksheet to work a macro 2

Back
Top