0

How can I pass every value from combo box through a sheet and retrieve a single cell that will be stored on a separate sheet?

Sheet 1: Enter a Location from Combo Box (Stored on Sheet 2) and date to retrieve hourly temperature from external database for 90 days starting from date that was inputted. Stores maximum value. Sheet 3: Need to go through each location and store max value next to Location name,

Right now, My macro for one iteration is: 

  ActiveWindow.SmallScroll Down:=-6
    Range("B1").Select
    Selection.NumberFormat = "General"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "='Load Book'!R[2]C[-1]"
    Range("B2").Select
    Sheets("Max Amps").Select
    ActiveSheet.Calculate
    Sheets("Load Book").Select
Range("B3").Select
Selection.NumberFormat = "0"
ActiveSheet.Calculate

This is only working for whatever is in that one cell. And if I try to record a macro for the next iteration too, the first maximum temperature changes to be whatever is in the second location too. How can I get the max temperature store, then move to next location without first being affected? And how can I apply this to 400 locations?

samred
  • 3
  • 3
  • 4
    Start here: http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros . – Brandon Barney Apr 26 '17 at 13:47
  • 4
    Please edit the title of your question into something that actually reflects the problem you are experiencing. –  Apr 26 '17 at 13:52
  • @BrandonBarney look at this: `ActiveWindow.SmallScroll Down:=-6` that's just a recording. Don't go to far. Let the OP learn step by step. – M-- Apr 26 '17 at 15:00
  • @Masoud I know it is a recording, which is why I directed him to avoiding 'Activate' and 'Select'. This is how I learned about my bad habits at least, so I direct others in the same direction. – Brandon Barney Apr 26 '17 at 15:02
  • Useful comments, but since I'm a very inexperienced VBA user, I did not understand the link you pointed me to at all. – samred Apr 26 '17 at 15:09

1 Answers1

0

First change the code

     ActiveWindow.SmallScroll Down:=-6
    Range("B1").NumberFormat = "General"
    Range("B1").FormulaR1C1 = "='Load Book'!R[2]C[-1]"
    Sheets("Max Amps").Select
    ActiveSheet.Calculate
    Sheets("Load Book").Select
Range("B3").NumberFormat = "0"
ActiveSheet.Calculate
Aleksandr
  • 1
  • 3
  • This is not an answer to what OP asked. You did nothing except removing unnecessary selection (not even the scrolling part). You did not even `dim` the sheets to avoid selection of them. I would not even consider this as a bad answer. – M-- Apr 26 '17 at 15:03