VBA Code to copy a cell with a number >0 to the next available column and skip blanks

[Krown]

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?

 

[Joe4]

Welcome to the Board!

I am afraid your question does not make a whole lot of sense without seeing/understanding the structure of your data.
Can you please post a small sample of your data and expected results?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Krown]

Please bare with me because I am new to coding. I'm creating an inventory tracking worksheet where I can track how long it takes to go through a bottle of product. I also want to be able to save this data so that I can look for trends in usage time.

I'm trying to find an easier way to track months-in-use of a product (Antibody) with open dates and end dates. After I calculate the time (in months) I want to move that number over to the "Time open data" section in the spreadsheet. Is there a way to copy the "Time open (months)" number to the same row under "Time open data" - copy D2 to F2. But I also want it set up so that F2 remains the same and when I change the input numbers to calculate a new number for D2, this new D2 will copy and be put into G2, etc.

There are 150 different products that I have to track, so I'm trying to create a system that is less manually wherever I can make it. Thanks in advance.

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

 

[Joe4]

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?

 

[Krown]

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?

Sorry, I didn't have it completely formatted. Below has the real calculation for "Time open (months) in the table.

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.

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

 

[Joe4]

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.

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.

 

[Krown]

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.

Thanks for trying to help. I would like it to work like this:

B2 will be entered first. Then when C2 is populated, it will calculate the number into D2. When D2 is populated, I would like that to copy to F2 where it will remain there. Then I will delete the dates in B2 and C2.

Then I will start the process over again - Enter B2. Enter C2. Number will be populated in D2. D2 will now copy into G2 where it will remain.

I want it to become a loop where after each process, the number from D2 will become H2, I2, J2, etc.

Am I clearly explaining this? Thanks so much.

 

[Joe4]

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:

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

It will run automatically as you manually update the values in columns B and C.

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:

=IF(AND(B2>0,C2>0),DAYS(C2,B2)/30,"")

 

[Krown]

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:

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

It will run automatically as you manually update the values in columns B and C.

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:

=IF(AND(B2>0,C2>0),DAYS(C2,B2)/30,"")

This works. Thank you so much!

 

[Joe4]

You are welcome!
Glad I was able to help.

You can see that I added lots of documentation in my code (the green lines) to try to explain what is happening at each step so you can follow along.