At every blank row, calculate the beginning stock and the ending stock and take the change

[goodluckboss]

stock
16967
16967
16967
16967
16967
17011
BLANK ROW
7702
7703
7711
7783
BLANK ROW
225845
225846
225863
225870
225878
225880
225880
225881
225874
225875
225894
225896
225905
BLANK ROW

How do I create a vba that takes the difference bteween the bottom row and the top row before a blank row to calculate the change in stock level and insert that change in the blank row cell

[bebo021999]

With "stock" in A1
Value from A2 downwards

Option Explicit 
Sub test() 
Dim Lr&, top&, f, cell As Range 
Lr = Cells(Rows.Count, "A").End(xlUp).Row 
Set f = Range("A2:A" & Lr).Find("") 
top = Range("A2").Value 
    For Each cell In Range("A2:A" & Lr + 1) 
        If cell.Address Like f.Address Then 
            cell.Value = cell.Offset(-1, 0).Value - top 
            top = cell.Offset(1, 0).Value 
        ElseIf cell = "" Then 
            cell.Value = cell.Offset(-1, 0).Value - top 
            top = cell.Offset(1, 0).Value 
        End If 
    Next 
End Sub  

[goodluckboss]

Thank you. Let me try it out right now

[CheeseSandwich]

Another option:

Please Login or Register  to view this content.

[goodluckboss]

Thank you! It works great!

[goodluckboss]

Hi there is there a way to amend the vba above to ON VISIBLE ROWS as sometimes I may filter my dates say After 9 March but the VBA still runs based on all rows instead of visible rows only

[CheeseSandwich]

Try:

Please Login or Register  to view this content.

[goodluckboss]

Thank you let me try it now

[goodluckboss]

Attachment 771913
Attachment 771914

Hihi I get a type mismatch when I run it.

Could it be because the header row is a text?

[CheeseSandwich]

Have a look at it working on the attachment - it may give you a clue as to the issue you are having.

[goodluckboss]

Have a look at it working on the attachment - it may give you a clue as to the issue you are having.

Hi there not sure if it is my table that is having the problem. I keep getting the error.

I have attached my spreadsheet without the rest of the data for you to have a look. So sorry. I tried pasting the data over to your worksheet and it works but when it is on my worksheet there seems to be a problem.

[CheeseSandwich]

Ah I see,

As you are using a table, the xlup bit of the code will see the bottom row of he table as populated even though there is no data, if this blank row is always on the table then you should only nee to change the row below:

From:

Please Login or Register  to view this content.

To:

Please Login or Register  to view this content.

[goodluckboss]

I see thanks for that as I am using Powerquery to populate that table. now I see why there is a type mismatch coming out let me try it when I get home. Thank you so much. )))))

[sintek]

As you are making use of a Table then ....

Please Login or Register  to view this content.

[goodluckboss]

Hi there something seems wrong. I wanted it to appear on the same spreadsheet and I tried changing to this code and I guessed I did something wrong

Please Login or Register  to view this content.

What did I do wrong? Everything changed to the same value on that columnAttachment 771965

[goodluckboss]

As you are making use of a Table then ....

Please Login or Register  to view this content.

Getting an error Type Mismatch at the bottom line

Val = Data(i, 2)

By the way how do I choose the columns which I want to appear at?

[sintek]

Getting an error Type Mismatch at the bottom line
Val = Data(i, 2)

The code was supplied for your uploaded file in post 11...It works...Only way it wouldn't is if you changed data format...hence variable not accepting data type...

By the way how do I choose the columns which I want to appear at?

Code supplies result based on Column 2...If you want it for Column 1 then change the 2's to 1's...

Upload sample file that produces the error...I don't want to declare Val as a variant just to hold an error...

[goodluckboss]

Hi there it does work found the error as my Column 2 was a name column.

However, when I ran it on the excel that was provided, try removing value 110692 from Column B leaving all the rest around and running the macro. Not sure why it makes it all jumbled up

Thanks so much for helping. I like that arrow and small pc button you created for me lolz.

Any filter that shortens the row causes everything below to become NA

Like if you remove 7685 from Column A, it will cause everything below it to be wrong

[sintek]

try removing value 110692 from Column B leaving all the rest around and running the macro. Not sure why it makes it all jumbled up

Like if you remove 7685 from Column A, it will cause everything below it to be wrong

Nope...all works as it should...For Col B...Not sure which file you are using...
Upload sample file that produces the error...

[goodluckboss]

Nope...all works as it should...For Col B...Not sure which file you are using...
Upload sample file that produces the error...

Weird on my way home now. Will test it again. I was using the file that you uploaded to test. Let me try it again. And see if I can get a screen capture of it

[goodluckboss]

Manage to replicate it. Have a look. Perhaps I am doing something wrong

https://streamable.com/z71krb

[goodluckboss]

I have filtered 1 row away and marked it as red and have not clicked on the VBA so you can see

Thank you

[sintek]

How do I create a vba that takes the difference bteween the bottom row and the top row before a blank row to calculate the change in stock level and insert that change in the blank row cell

You were provided code for this...above

I have filtered 1 row away and marked it as red and have not clicked on the VBA so you can see

You mean hide...not filter...code still runs and produces result of 3 ... 17295-17292

If you are wanting to changing the criteria...Obviously code will not work...

I suggest you explain in step by step detail what you are wanting...This is different to your initial request???

[goodluckboss]

Hi there sorry about my wordings. I thought filter was hide as I was using the auto filter.

This database is actually a database of daily scrape of a specific product or products.

It will have dates in the first column
and stock level for the second column

I wanted to check the drop in stock levels for each product

However, there may be times I want to hide or filter dates to see what is the difference in stock count

So if the whole table is from 1 Jan 2022 to 31 Jan 2022

There may be times I would like to check how much did stock drop from 13 Jan 2022 to 23 Jan 2022 which is why I need to hide the rows

Most of the codes here worked except when I hide it, it produces weird results ))

[sintek]

Most of the codes here worked except when I hide it, it produces weird results ))

We gonna go around in circles wasting time...So like I said...Upload this file with these weird results showing...as I have no idea what you are referring to...
I just wish members would state the exact requirement and expected result with accurate data from the get go...

[goodluckboss]

Here you go

sorry about the round about and not explaining myself clearly.

The initial problem was solved but I found out that when I "hide" the rows, it does not work so I elaborated on that as I did not expect that this issue will happen.

[sintek]

means nothing...

like I said...Upload this file with these weird results showing...as I have no idea what you are referring to...

do you not understand the above request?

What is it you actually wanting to achieve...Filter on dates and get a stock diff...open vs close or do you want blank rows with diff showing...

[goodluckboss]

It's alright Sintek,

Thank you for your time

[sintek]

100 % of the time a solution is offered when we are able to understand your request...You are not forthcoming or transparent...
Still no expected result showing...In your brain...I am not???

This seems to be the norm with you...Looking at all your other posts...Wasting members' time...