I'm new to learning VBA and I'm trying to manipulate the data in my spreadsheet that I'm building. I want to track inventory usage raw data by copying the calculated result to the next available column excluding blank cells. How can this be done?
Sample Inventory.xlsm | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | |||
1 | Antibody | Open date | End date | Time open (months) | Time open data → | |||||
2 | Antibody 1 | 12/1/2023 | 12/28/2023 | 3.0 | 3.0 | |||||
3 | Antibody 2 | 3/4/2023 | 11/2/2023 | 3.0 | ||||||
Sheet1 |
Sorry, I didn't have it completely formatted. Below has the real calculation for "Time open (months) in the table.Sorry, I have been away a bunch for the Holidays.
How/when exactly is "Time open (months)" calculated?
I am not seeing how you get to 3 months from the data you are showing.
When exactly is column F calculated/populated?
You talk about then "freezing the value in column F", as you may change the values in the other columns in the same row.
At what point would then a populated value in column F be re-calculated to a new value?
Sample Inventory.xlsm | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
1 | Antibody | Open date | End date | Time open (months) | Time open data → | ||||
2 | Antibody 1 | 12/1/2023 | 12/28/2023 | 0.9 | |||||
3 | Antibody 2 | 3/4/2023 | 11/2/2023 | 8.1 | |||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
D2:D3 | D2 | =DAYS(C2,B2)/30 |
It usually can, if you use VBA, but you would clearly need to define exactly how it is supposed to work.Ideally, D2 would copy to F2. Then if I change the dates in column B2 and C2, the new number in D2 would copy to G2, etc. I'm trying to track data but I'm not sure if Excel would have the capacity to do this the way that I'm describing. Thanks so much.
Thanks for trying to help. I would like it to work like this:It usually can, if you use VBA, but you would clearly need to define exactly how it is supposed to work.
For example, you could wait until both B2 and C2 are populated before moving the calculation in D2 to cell F2.
But then after that point, when would you move the next calculation to cell G2?
Would it be when JUST cell B2 is updated? Or when cell C2 is updated?
If you have VBA do it when either is updated, the update to B2 will populate G2, and then the update to C2 will populate H2.
Is that what you really want?
If you can CLEARLY and COMPLETELY define the rules for exactly how this should work (in plain English), we can probably come up with the VBA code you need for this.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Long
Dim c As Long
' Exit if more than one cell updated
If Target.CountLarge > 1 Then Exit Sub
' Capture row of update
r = Target.Row
' See if update made to columns B or C after row 1
If (r > 1) And ((Target.Column = 2) Or (Target.Column = 3)) Then
' See if both columns B and C are populated
If Cells(r, "B") > 0 And Cells(r, "C") > 0 Then
' Check to see last column populated in row
c = Cells(r, Columns.Count).End(xlToLeft).Column
' See if before column F
If c < 6 Then
' Populate column F with value from column D
Cells(r, "F").Value = Cells(r, "D").Value
Else
' Populate next column with value from column D
Cells(r, c + 1).Value = Cells(r, "D").Value
End If
End If
End If
End Sub
=IF(AND(B2>0,C2>0),DAYS(C2,B2)/30,"")
This works. Thank you so much!OK, as long as that is the way you are populating it, here is code that will copy the value from column D to columns F, then G, H, I etc.
It will only populate it when a value is entered into columns B or C AND both columns B and C have values.
So if you are populating it as you described in your last post, it should work.
This code must be placed in the Sheet module. An easy way to ensure you are putting it in the correct place is to go to the sheet you want to place it in, right-click on the Sheet tab name at the bottom of the screen, select "View Code", and paste this code in the VB Editor window that pops up:
It will run automatically as you manually update the values in columns B and C.VBA Code:Private Sub Worksheet_Change(ByVal Target As Range) Dim r As Long Dim c As Long ' Exit if more than one cell updated If Target.CountLarge > 1 Then Exit Sub ' Capture row of update r = Target.Row ' See if update made to columns B or C after row 1 If (r > 1) And ((Target.Column = 2) Or (Target.Column = 3)) Then ' See if both columns B and C are populated If Cells(r, "B") > 0 And Cells(r, "C") > 0 Then ' Check to see last column populated in row c = Cells(r, Columns.Count).End(xlToLeft).Column ' See if before column F If c < 6 Then ' Populate column F with value from column D Cells(r, "F").Value = Cells(r, "D").Value Else ' Populate next column with value from column D Cells(r, c + 1).Value = Cells(r, "D").Value End If End If End If End Sub
Also note: If you want your formula in column D to only show a value when BOTH columns B and C have values, you can update your current formula to this:
Excel Formula:=IF(AND(B2>0,C2>0),DAYS(C2,B2)/30,"")