ive been writing a database for my business using excell and VBA
I have worksheets for clients surpliers labour and parts i have written userforms that add parts clients and surpliers to their respective sheets
I have started to white a userform that ties all this information together to create invoices it creates a new invoice number allows me to select which client the invoice is for and add labour to the invoice.
I have a txtbox called "txtsearchparts" and a button called "cmdSearchparts"
at this stage i can type a serialnumber into the "txtsearchparts" box and click the "cmdSearchparts" button. This saves the text that was entered into "txtsearchparts" as a variable called "partsearch".
I am having trouble with the last bit. once the serial number has been entered , the button has been clicked and the serial number has been saved in "partsearch" variable, i want it to search the first column of the "parts" worksheet the the serial number contained in the variable and return the row address that the part stored on to a variable called partaddress.
I'm trying to find a way to use a macro to extract data from a specific column from Sheet1 based on the columns header/title and copied into Sheet2 into the respective column with matching header/title.
For instance, in Sheet1:
Chicken Cow Donkey Pig
[Code]....
So the above table would be the result i'm aiming for.
NOTE, its not different workbooks. I'm looking for sheet to sheet macro.
I've attached a file as well if someone wants to have a go at it. There are no codes in it.
In this example, i need to get rid of all the rows except for the mfg part #. The heading "mfg part #" can be deleted as well.
The number of rows will vary each time i capture data. So for this example, i need 50 rows of data, but the next time i capture it, i might need to only keep 10 rows of data. The format will remain the same, the only thing that changes are the number of rows and the actual part #'s.
Currently i hav list of info that is long like this.
A 1 B 2 C 3 D 4 E 5 F 6 G 7 H 8 I 9 J 10
How to move row to column example below. Because i want the data to be printed on an a4 paper which can fit around 3 row to save space . If i manually copy paste page by page it is too much.
A 1 D 4 G 7 B 2 E 5 H 8 C 3 F 6 I 9
===
Currently i have macro code which roughly does this.
A 1 B2 C3 D4 E5 F6 G7 H8 I9 J10
Sub test() Dim rng As Range, m As Integer, c As Range Columns("c:E").Delete m = 2 Set rng = Range(Range("a1"), Range("a1").End(xlDown)) Set c = Range("a1") Do While c <> "" 'MsgBox c.Address
Range(c, c.Offset(m - 1, 0)).Copy Cells(Rows.Count, "c").End(xlUp).Offset(1, 0).PasteSpecial Transpose:=True Set c = c.Offset(m, 0) Loop End Sub
I have a set of data (time in minutes) below (fig 1) that I need to use if the total time between two entered times matches a coloms total. If it matches I want Excel to use the data in that colom to affect the data in another (fig 2) colom adding the times to each cell.
Looking at both figs. below I want Row B, Colom A in fig 2 to add Row A, Col A to Row A, Col A in fig 1 since both are the total of 19.
fig 1 Coloms A B C D Row A 5 5 6 6 Row B 5 6 6 8 Row C 4 5 6 7 Row D 5 4 4 5 Total 19 20 23 26
fig 2 Coloms A B C D Row A 1:00 2:00 3:11 4:01 Row B Row C Row E Row F 1:19 2:20 3:33 4:27 Row F-A= 19 20 23 26
This looks up if Player 1 was in the top 3 scoring zones and adds up the players total score.
I have another column in this table of data labelled 'Number of Players'. I was wondering how to lookup the total score for this player in the games he played under 6 players for example and the games he played in where there were other six players.
I've tried using that formula ^^ with variations of IF and SUMIF formulas to try and figure it out, but I keep getting formula errors or it just returns with the total score, and not just with the total score under 6 players.
I am also trying to create a graph of date on the x axis against score to date on the y axis - everytime I try, the x axis is always numbers instead of the date?
I want it to count and fill in a range in column A until it sees a blank or notices the change in value in column B. In the example below i hope it shows what i need to do. i left the last group without numbers to show that is where it needs to start counting over again. i am basically wanting to count down 1st place 2nd place etc.
Any way to search for a value down column A, another value across roW 1, and tells what value lies in the cell at their meeting point. 4 example, I'm looking For "47" somewhere in column a, and also the number 53 somewhere in row 1, and I need to know what value is in the cell where those to meet.
Is it possible to have a column count up if two other cells meet criteria?
What I would like to happen (if it is even possible) is, if cell B1 equals CO and C1 is a duplicate, then G1 enters #1 for the first time it is duplicated, #2 for the 2nd time it is duplicated, etc.
Is this even capable of happening.
I am attaching my spreadsheet (TAB "AG Orders"). I filtered the spreadsheet so you can see an example of what I am looking for. I manually typed in column G but I was hoping these numbers could automatically populate.13-14 budget-AG test.xlsm
How to do what I am about to ask takes time and time causing brain overloading. I have made screenshots but I can't upload them. I get red exclamation point with images sizing ~55KB
I've been writing a UserForm in Excel that serves the purpose of a front-end contact/reporting spreadsheet.
I've managed to get a multitude of elements to work together as of yet, however have been having problems writing data to the correct place on the spreadsheet.
What I've already achieved:
The first interactive portion of the userform currently has a TextBox (ArtistAgentAddEmail) - this is the email that we would like inputting on the spreadsheet.
a ComboBox which is populated on 'Userform_Initialize' into an array (ArtistAgentAddDestination) - The range of this array is A1:AC1.
[Code]......
What I would like to do next:
The issue that I've been having is getting the email to be written into the correct column. Once I have typed in my email and chosen my destination from the combobox, I would like to write this email address in the column of the destination chosen.
E.G. The first 5 elements of my ComboBox array are: Neil O'Brien, CAA, CODA, ITB and PRIMARY. If i got an email from somebody in the CAA organisation to add a new contact to the reporting, I would like to be able to add this address via the userform underneath the previously added contact.
The code that I am currently using for my confirm button is:
I have a spreadsheet with my Periods along row 10. e.g. C10: "1", D10: "2", E10 "3", F10: "4", G10: "5" etc. (green on the attached sheet). I have my departments along column B, e.g. B11: "Baked" B12: "Fresh" B13: "Frozen" (yellow on the attached sheet)
what I need and cannot work out is some VBA code that will populate two variables (lets call them Period & Department) when I click on one of the figures. For example if I click on cell: if I click E14: Period would have the contents of cell E10, and Department the contents of cell B14.
if i click G14: Period would have the contents of G10, and Department the contents of cell B14 again. I know how to get the click on the cell to work properly etc, and I have code to slot these variables into that works very nicely, I just can't get this bit to work!!!!
Is there a way to make multiple charts off of 1 big data sheet. Sounds easy but there is more.
Sheet name is Field Column A is Name ( about 40 different names) Column B is Date (x axis primary and secondary) Column D is Oil (y primary axis) Column G is Water (y primary axis) Column E is Gas ( y secondary axis) Column M is MCFE (y secondary axis)
There is about 2-3 thousand rows, updated monthly. I need to make a graph by each well name.
1) can I make multiple graphs that can just look at the "field" sheet and then breakout just the 1 well name that shows up on the chart sheet that I create
I've tried pivot charts but you can't have secondary acess because it resets every time.
Basically, make multiple charts that will graph the data of a specific name in column A
I have a sheet with a country in it in A1 (validation list).
Depending on the country in A1 a country specific IF command has to be executed on a column1 with first cel = A3. This IF command also relies on the values in column 2 and 3 on the same row. (B3 and C3 are in the IF cmd)
I taught to do it like this : =IF($A$1="Spain";$A$5;0) with A5 being the country specific IF command which should be executed when spain is selected. however when I do this, this only works for the first cell ( I cannot drag this formule down, since it will always give the value calculated in column 2 and 3 on row1.
I'm not sure if the best way to 'select' the country specific IF cmd is with another IF command..
I have rather large Excel file that spans around 245 columns & has multiple users responsible for certain columns. Columns are chunked to provide data about a specific group. Every 72 hours, I need to provide to managers what’s currently on this file in a specific summary format.
I’ve recorded a macro that gets me half way there. Here’s where I’m clueless. After my macro deletes unnecessary columns, what remains are the columns for “Main Data” group + each of the 5 “Business” groups (Investors, Lawyers, Credit, Finance, and Support) which would have identical column headings.
(1) Name (2) Address (3) ID# (4) Control#
Furthermore, for each of the 5 “Business” groups, there are 9 slots (ie- a user can enter up to 9 unique entries for a particular group.)
Example- The Investor group contains 9 slots. Each slot will contain the 4 columns mentioned above. So there are 46 slots in total – 1 for Main Data, 45 (5 groups x 9 slots) for Business groups.
MY PROBLEM: I need a macro to now cut & paste all data from each of the 45 Business groups slots & then paste at the bottom of the MAIN DATA slot.
The final file would be 4 columns (as listed above) wide. Data from each of the 45 business group slots would be one below the other- all of it underneath the data in the Main Data group.
I attempted to record a macro where I’m copying a section’s data range starting from row2 to row4000 (I know there will never be 4000 entries. I did this just to ensure that all future data would be captured) then pasting that after the last entry in the Main Data.
Example- I’d copy data from Investor1 slot(starting at column E2-H2) & go about 4000 rows down. Then I’d press CTRL+Down Arrow key in column A & then click the down arrow one time to take me into 1st empty cell where I would paste my copied data. I was going to repeat this process for the remaining 44 slots.
I’ve added some extra entries to test the macro & the problem is that the recorded macro is pasting data in specific cell location in column A instead for looking for the 1st empty cell in column A & then pasting the copied data.
So far I've gone thought the net & tried to paste in a few VBA posts/tips that I thought would work. Here's what I have so far from tinkering around. I need someone to tell me what to replace the xxxxxxx line with. This would be the language to tell Excel to paste copied data range into the first blank cell in column A. I've gone thought the net & tried to paste in a few VBA posts/tips that I thought would work.
Here's what I have so far from tinkering around. I need someone to tell me what to replace the xxxxxxx line with. This would be the language to tell Excel to paste copied data range into the first blank cell in column A.
Sub transposedata() Sheets("ConsolidatedYTDReport").Select Range("E2:H4202").Select Selection.Copy XXXXXXXXXXXX ActiveSheet.Paste End Sub
I have a row (will always be row 3) where each cell contains a day of the week, the days repeats for a year or so, making the row almost 400 cells.
Like this, Mo - Tu - We - Th - Fr - Sa - Su - Mo - Tu - We - Th - Fr - Sa - Su - and so on...
Though, A3 doesn't have to be "Mo" because the days in this case can change (A3 can start with "Tu"), hence I think I need a macro.
So if this row contains a weekend, "Sa or "Su" I want all the cells in the column beneath that which contains a specific value to be cleared.
Example, if "Sa" or "Su" has 3 values in the columns under them, all the values that contain "X" or "Y" has to be cleared.
Like this: Rows (1,2,3...,) 1---- 2---- 3 Mo - Tu - We - Th - Fr - Sa - Su - .. and so on.. 4 A --- B --- X --- Y --- X --- B --- Y 5 A --- B --- X --- Y --- X --- X --- X 6 A --- B --- X --- Y - --X --- Y --- C
After the macro it should be:
1---- 2---- 3 Mo - Tu - We - Th - Fr - Sa - Su 4 A --- B --- X --- Y --- X --- B --- 5 A --- B --- X --- Y --- X --- --- 6 A --- B --- X --- Y - --X --- --- C
Currently I am using the Kickbutt VBA Find Function of Aaron, but I would like to have something that works more efficiently. What I currently do is (assuming all possible values for Column J are A - F):
although I just want some code that says: delete all rows except those that have "F" as content in Column J. I already tried something like:
Range("1:65536").Select For Each cl In Range("J:J") If cl.Text = "A" Or cl.Text = "B" Or cl.Text = "C" Or cl.Text = "D" Or cl.Text = "E" Then Rows(cl.Row).Delete End If Next
but it also takes much to long. The major problem I think, is that the number of records is variable so I search the entire worksheet...
I have number in cell (A1) = 100. when I enter number in any cell of column(B) for example (B1)=10. then in cell (C1) the result of (A1) - (B1) = (90) and if I add in cell (B2)=10 then in cell (C1) the result of (A1) - (B1+B2) = 80 .accumulatively in cell (C1). and any number in column (B), the result will be (A1) minus any number in column(B) accumulated in (C1)
second question
I have number in cell (A1) = 100. when I enter number at cell (B1) = 10 then the result would be in the adjacent cell (C1) = (A1) - ( B1) = 90 and If I enter a new number in cell (B2) = 10 then the result would be in the adjacent cell (C2) = (A1) - (B1+B2) = 80 and If I enter a new number in cell (B3) = 10 then the result would be in the adjacent cell (C3) = (A1) - (B1+B2+B3) = 70 and so on. I want the result to be add automatically to adjacent cell in column (C)
I have a worksheet which basically tracks time. the time is reported in Column C. In that row in Column E, there is a validation list with about 6 different categories in it. On the side of this "table" I have a list of all the categories and I want a value to be next to it that reports the sum of time (C) for each category (E).
So for the "Routing" category, I would want the value to be the sum of just data on the timesheet that have "routing" in Column E.
I am trying to find a solution for highlighting cells in a column that are repeats, ie. >3. I also need these cells to only be highlighted if the adjacent cell in the next column contains specific text. I have tried using conditional formatting with a countifs formula to no avail.
Is there a way to go to a specific column i excel? My data ranges from Column A to Column TP and rather than using find, or scrolling along to find the column i want, i want to know if i can put a date in A1 for example and it will go straight to that column?
I have used a macro before to filter horizontally, but thats not going to work in this case, i just want to go straight to that column labelled "05/03/2014" for example.
