User Profile
BlueCollarVending
Copper Contributor
Joined 2 years ago
User Widgets
Recent Discussions
Help with data sorting VBA
Ineed help with data sorting vba. I have a "Reports" tab that automatically generates data from other tabs to show low qty items. But at the moment the info is sorting by default (AZ, column A) but i would like it to sort by quantity instead. Here is the vba i currently have. Private Sub Worksheet_Activate() Application.ScreenUpdating = False Dim invws As Worksheet Set invws = ThisWorkbook.Sheets("Inventory") nrows = WorksheetFunction.CountA(invws.Columns(1)) 'nrows = invws.Cells(invws.Rows.Count, 1).End(xlUp).Row Range("A7:I50000").ClearContents k = 7 For i = 2 To nrows If invws.Range("E" & i) = 0 Or invws.Range("I" & i) = "Yes" And invws.Range("A" & i) <> "" Then Range("A" & k) = invws.Range("A" & i) Range("B" & k) = invws.Range("B" & i) Range("C" & k) = invws.Range("C" & i) Range("D" & k) = invws.Range("D" & i) Range("E" & k) = invws.Range("E" & i) Range("F" & k) = invws.Range("F" & i) Range("G" & k) = invws.Range("G" & i) Range("H" & k) = invws.Range("H" & i) Range("I" & k) = invws.Range("I" & i) k = k + 1 End If Next i Application.ScreenUpdating = True End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) Target.Calculate End Sub925Views0likes3Comments[SOLVED] Expense Report
So i have a expense report im working on and i need help with some formulas. The report is multiple sheets, 1 sheet is an overview 1 is the main receipt entry 2 are for more detailed entries (based on the receipt type) 1 Milage Log 1 settings So what i am trying to do is, on my main receipt entry sheet, i enter the receipt basic info. Date, Receipt number, supplier, type, pretax amount, tax and then totals are automatic. on the 2 detailed sheets i would like to reference the main sheet and have it pull info from that sheet (based on "type") and have it auto populate the info there. then i would go in and add the details based on the receipt ( ie. all the items on the purchase). Then i would "group" the purchase details under the auto populated info. I guess my first question would be, If it were to set this up, would it mess up my formulas if i add rows under the auto populated info and group them? I understand this is probably a large request and a lot of formulas. Any advice or direction would be awesome.2.5KViews0likes9Comments[SOLVED] vba debug help
Hello all. So i have a form that i use to input information for inventory. Im having a issues on some of the inputs. Im needing help writing a code that will pop up a message box (instead of debug box) that will say to please enter "xx" information. At the moment, if i forget to enter a date in the date box, and hit enter, i receive a debug box and it wants me to debug the issue. But i would like to change that to a msgbox telling the user to enter all the needed info. Also is there a way to make it generic so i can insert it into other parts of the code? Any advice would be appreciated. I guess what im trying to say in so many words, is there a way to put a validation in place for each of my form boxes to make sure they have a entry? and if they dont, to show a msgbox saying " please input a -whatever box is blank-" instead of having the debug window show? Heres the section of code: Im having the issue with row 13 but would like a generic formula validation for rows 11-14 Private Sub add_Click() Set invoutws = ThisWorkbook.Sheets("Inventory In-Out") Set varws = ThisWorkbook.Sheets("VARIETY PACK") Set invmasterws = ThisWorkbook.Sheets("Inventory MASTER") nrowmaster = WorksheetFunction.CountA(invmasterws.Columns(2)) + 1 nrowvar = varws.Cells(varws.Rows.Count, 2).End(xlUp).Row nrows = WorksheetFunction.CountA(invoutws.Columns(1)) 'Cells(invoutws.Rows.Count, 1).End(xlUp).Row If Me.codeval <> "" Then invoutws.Range("A" & nrows + 1) = Me.codeval invoutws.Range("B" & nrows + 1) = Me.typeval invoutws.Range("C" & nrows + 1) = DateSerial(Year(Now), Split(Me.dateval, "-")(0), Split(Me.dateval, "-")(1)) invoutws.Range("D" & nrows + 1) = Me.qty * 1 = ""Solved893Views0likes2Comments- 833Views1like0Comments
Re: [SOLVED] Expense Report
I'm not sure i follow what you are asking. If you are referring to the "Inventory and Other" tabs, those details i want to come from the purchase report automatically. As far as the "Total" columns on the Purchase Report, That is generated automatically from the report page.2.3KViews0likes1CommentRe: [SOLVED] Expense Report
I tried the formula you posted and it worked but only for a specific cell. And it doesnt allow me to filter the Main PURCHASE REPORT by category and input all the essential data with the choose function. Also what would a formula be to make it autoskip a row? I asked ChatGPT and this is what it sent. Took a bit to get there but.... =IFERROR(CHOOSE({1,2},INDEX('Purchase Report'!A:A,SMALL(IF('Purchase Report'!D:D="Your_Category",ROW('Purchase Report'!D:D)-ROW('Purchase Report'!D1)+1),ROW(INDIRECT("1:"&ROWS('Purchase Report'!D:D))))),INDEX('Purchase Report'!C:C,SMALL(IF('Purchase Report'!D:D="Your_Category",ROW('Purchase Report'!D:D)-ROW('Purchase Report'!D1)+1),ROW(INDIRECT("1:"&ROWS('Purchase Report'!D:D))))))), "") The problem is, is it keeps causing my excel to crash. Plus i dont really understand any of this. I would like to understand some of the code so i could figure out how to make it work properly.2.4KViews0likes7Commentsformatting help
Hi everyone. I need help with my excel table. I've created a financials workbook for my business and need help keeping it in order. I want to have the data from the "Purchase Report" auto populate to the appropriate tab based on a criteria. I have a tab for inventory and one for other. I want to set it up so when i add a purchase on the main report, it will auto generate it to the proper tab with a line between each auto entry so i can add in the details about the entry. Will it screw up the formula if i insert rows between the formulas on the "inventory and other" tabs? So i can add the itemized data per entry. Is there a will to have it pull multiple lines of data as well? I would like to have it pull the "date, receipt number, supplier, and total" any help is appreciated780Views0likes1CommentRe: How to automatically update different tabs from master tab in Excel?
I was dealing with the same issue with a financial report i was building. I had the months spread across multiple tabs and was running into issues. I later redesigned the whole report and took all the months and set them up on a single sheet. But i basically took each month and added a group under that month. So when collapsed, it would only show the month title with a totals cell. When expanded, it showed all the details for that particular month and i was able to add in rows if need to expand the groups volume.641Views0likes0Commentsconditional formatting
Hello. So I wanted to know if there was a way to have my totals column deduct an amount if it has a conditional formatting met? Ex; I have a expense report that has business purchases and at the bottom I have the totals. On the side I have a return column (for items I've returned). I have a conditional format that states "if a "yes" is selected on the returns column, then it changes the color of the text and puts a strike through on the line. BUT what i would like to add is for the items that are being "returned" to have that amount DEDUCTED from the totals at the bottom automatically as well as have that "returned" amount added to a another totals column. I understand that there are multiple formulas at play here. I need a place to start. 1. have to have the totals column reference the returns column 2. need to decided if it meets a criteria, return "yes" or "no" 3. if it meets the "yes" then it will deduct the amount of that row from the totals 4. then it will take that "deducted amount and add to a different totals box (which im assuming the formula for that would need to be in the "returns total" box and would reference the "returns" and the "totals") If there is anyone that could at least help get me pointed in the right direction, that would be appreciated. thanksSolved591Views0likes2Comments
Recent Blog Articles
No content to show