Running anothe subroutine via a macro

[ProphetofProfit]

I've honestly searched every page on the interwebs so far, tried about 10 different approaches but my ineptitude with VBA wins every time.

Everything up to 'application run' works fine.

Sub BackTest()
Dim i
Dim r
For r = Sheets("Summary").Range("F1") To Sheets("Summary").Range("F2")
 
  Sheets("Data").Cells(1, 3).Value = Sheets("Odds").Cells(r, 7).Value
  Sheets("Data").Cells(2, 3).Value = Sheets("Odds").Cells(r, 8).Value
  
  Application.Run "'Get Odds!'CmdCalculate_Click"

The subroutine I'm trying to run:

Sub CmdCalculate_Click()
    For i = 0 To 11
        For j = 0 To 11
.
.
.

I've tried lots of different approaches but no luck so far.

 

[Norie]

Where is the code for the sub you are trying to call located?

 

[ProphetofProfit]

It's in another sheet in the same workbook.

 

[Norie]

Have you tried just calling it?

Call cmdCalculate_Click

Another thing you could try us moving the sub to a standard module and renaming it.

Then you should be able to call it from anywhere, including the original Click event/sub.

You might need to change it slightly, depending on the code and what it does.

 

[ProphetofProfit]

If I use

Call CmdCalculate_Click

I get 'Compile error: sub or function not defined' which I don't understand.

 

[Norie]

That's probably because the code is in a worksheet module.

Have you tried the second suggestion?

That should work, but like I said the code might need to be slightly altered.

To tell if it does need altered we would need to see the code.

 

[ProphetofProfit]

Sorry, missed your second suggestion. Moved the sub to a module, edited the sub name and bingo! it works.

Hi5!

 

[ProphetofProfit]

I promise that all the good will I receive will be paid forward to other unfortunates that I can help.

My macro needs to activate cell A32 of worksheet "data", then use a do..loop until a specific value is found, then offset (0,1) and (0,2) and copy these values.

But it's bugged, again, at the first line, which is the simplest and leads me scratching my head.

  Sheets("Data").Cells(32, 1).Activate
Do Until Sheets("Data").ActiveCell.Value = Sheets("Odds").Cells(r, 18).Value
  Sheets("Odds").Cells(r, 21).Value = ActiveCell.Offset(0, 1).Value
  Sheets("Odds").Cells(r, 22).Value = ActiveCell.Offset(0, 2).Value
  ActiveCell.Offset(1, 0).Select
Loop

 

[Norie]

Well you rarely, if ever, need to activate/select anything in Excel VBA.

Here's how that could would look without Activate/Select.

Dim rng As Range

Set rng = Sheets("Data").Cells(32,1)
 
Do Until rng.Value = Sheets("Odds").Cells(r, 18).Value
      Sheets("Odds").Cells(r, 21).Value = rng.Offset(,1).Value
      Sheets("Odds").Cells(r, 22).Value = rng.Offset(,2).Value
      Set rng = rng.Offset(1)
Loop

Now that might not fix the problem but give it a try and if you still have problems post back with details.

 

[ProphetofProfit]

Wow that is fantastic, thank you very much.

I will now go do some good deeds.