Missing Something Simple Retrieving Value in Active Cell and Assigning it to a Variable in VBA - Please Help

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

  • This isn't the finished code, I will need it to loop, but I can't even get it to return the first value!! I have a numerical value in F17 but I cant just say "w = Range("F17").Value" because I will need this formula to start at F17 and keep going until there are no more rows. Here's what I tried after Range("F17").Select and Range("F17").Activate didn't work:

    I hope you can see what I'm trying to do once it starts if I can get the first result. I will start with the first result in F17, take the absolute value of (the cell below it - active cell) and then when I put in the loop it should do the same but one row down so ABS(F19-F18) then next loop ABS(F20-F19) and so on summing the total. The problem in I cant use specific cell references in the loop because I need it to keep moving down an unknown amount of times (until there are no more values in the next cells). Any ideas???? I feel like I'm missing something so simple.

    Thanks all for your time and any assistance you may be able to provide.



  • Welcome to the Forum. Please read the Forum Rules to understand how the Forum works and why I have added Code Tags to your post

    All VBA code posted in the forum must be wrapped in code tags, which you omitted, including single-line code snippets.Be sure to use them in future posts.

    How to use code tags

    Just highlight all of the code and press the <> in the post menu above button to add the code tags.


  • As I said, I haven't wrote the code for the loop to create the sum but what this is for is and excel spreadsheet in which measurements are recorded and each time a new measurement is recorded the worksheet automatically carries the conditional formatting for the tolerances to the next line so you can select all the data without knowing how much there is simply with (Inspection_Record3[1]) so I could write  =sum(Inspection_Record3[1]) a cell for the sum of these measurements. Unfortunately I need to calculate the CpK as it changes when more measurements are added and it has to match the way Minitab calculates CpK and Minitab uses the "standard deviation within" to do this rather than a normal standard deviation (which would make this very easy if they did). Do this in Excel I need to find the sum of the "moving range" behind the scenes. The moving range is simply the absolute value of the measurement minus the measurement before it. I need the sum of all those values again with it constantly changing as more measurements are added. I know how to do that if I can just get the first one to work in a way that refers only to the active cell and an offset one row below the active cell otherwise the loop wont work right if it is always starting at a specific referenced cell. That's why I was trying to activate the first measurement cell outside where the loop would be so within the loop it would then just keep shifting one cell down until there were no more values.

    If anyone has any better ideas for a way to do this I'm open to any help or suggestions. I don't know why it seems like I'm just missing something easy to be able to get that first and next result without using a cell reference when assigning the values to variables. I thought activating or selecting the cell and then just trying to have the variable be equal to the active cell would work but with the code in my first post it isn't, it is still taking the value from the cell I put the function in and the value of the cell below that one. Activating or selecting a different cell to be the Active cell just doesn't seem to work the way I thought it would.

    Thanks for your time and any insight, it's always appreciated.


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!