I have have a problem in my vba code
if my activecell is on example range("W78") then it will go to range("I5")
I made a code but it does not work, i cant use offset cause i have may columns but they all refer to the same column "I"
this is my example: Range("I & activecell.row").value
I need to find a formula on cell A2 on the attached file. The formula should show me the count of Customers who buy SKU 1 only.
I tried to use filters across SKU (2-16) where there is "0" and then count all customers who have values >0 on Column B. But this takes a long time and I have to do similar tables quite a few times. I was just hoping if anyone can come up with a more efficient solution to it.
I am using Excel 2003 and I need to count occurrences involving two colums like this: on sheet "SALES", column I has values ranging from 8.00 to 10.00; column M has positive and negative values.
formula to count how many numbers in column I exist between 8.00 and 8.99 associated to a positive value in column M? And likewise for negatives?
I'm using Excel 2003 and I need to calculate the number of occurrences on according to different criteria in two separate colums.
I am on Sheet 3 and the data I am analyzing is on Sheet 1, titled "RATES". I wish to know how many occurrences are when the word "GB" appears on column B (cells B2 to B100) along with a value greater than zero on column M (cells M2 to M100). The word "GB" does not appear alone but is part of a string of text with different words, I think this is relevant.
I have this in Column A, with about 120 entries:
Company 1
Contact 1
Address 1
City, ST, ZIP 1
Phone 1
Fax 1
Company 2
Contact 2
Address 2
City, ST, ZIP 2
Phone 2
Fax 2
Company 3
Contact 3
Address 3
City, ST, ZIP 3
Phone 3
Fax 3
I want this:
Company 1 Address 1 City, ST, ZIP 1 Phone 1 Fax 1
Company 2 Address 2 City, ST, ZIP 2 Phone 2 Fax 2
Company 3 Address 3 City, ST, ZIP 3 Phone 3 Fax 3
all the way down.
I can't figure out how to record the macro to tell it to then skip the blank line, collect the next set of data, and put it in the next row. I can do it for two, but then it just replaces the first two with the next two and I lose data.
I have a massive data file with thousands of lines of data. Each line is a pair, for example
View 6 Replies View Relatedneed a quick macro to match rows in two columns as per demonstartion below.
View 9 Replies View RelatedExcell macro to change columns to rows example
From this:
A B C
1 3 5
2 4 6
to this
A 1
B 3
C 5
A 2
B 4
C 6
The macro code that will populate and input box and ask you which range of columns and range of rows you wish to hide, hide the columns and advise you via a message box that it has been completed
I saw a macro written in this forum where we can select 256 rows to interchange them to colums in sheet 2. I need my first 10 rows to be in first column and next 20 rows in second column in sheet 2 ,like that it goes on . I am a very basic user of Excel .Can i get a macro with some condition that helps me in the above case.
I have several walking routes that are labeled A through H. Each walking route has a different distance. .18 miles, 1.45 miles, etc. What i would like to do is be able to enter what routes are walked in a day and have it display the milage in a total cell. Example......
View 4 Replies View RelatedOur company gets excel spreedsheets with UPC numbers. The numbers are divided in parts or columns. Sometimes it's in 2 columns; sometimes in 3; and sometimes in 4 like this:
A_____B_____C____D
023__14444__779__9
023__14442__789__7
I'd like to be able to concatenate the digits and have the entire UPC number appear in a blank column to the immediate right and go straight down the line and calculate them all. I figured out how to do this with separate macros for 2, 3, and 4 digits or columns.
Sub Combine_2_Part_UPC()
Do While ActiveCell ""
ActiveCell.Offset(0, 1).FormulaR1C1 = ActiveCell.Offset(0, -1) & ActiveCell.Offset(0, 0)
ActiveCell.Offset(1, 0).Select
Loop
End Sub
Is there a way I could do this with just one macro, by maybe selecting the digits of the first UPC number in the first row of the data to cue the macro in as to how many columns to process?
(The UPCs are in the midst of a lol of other data like product cost, so a macro probably wouldn't be able to figure out how many to calculate on its own.)
Why does my macro mess up on second line C23 and not places Play Equipment or the amount in the right column it stays on row C22
[URL]....
Code:
Private Sub CommandButton1_Click()
Dim SheetName As String
SheetName = "Estimate1"
SheetName = InputBox("enter the name of a sheet to use", "sheet name", SheetName)
[Code]....
I'm trying to create a macro that will add a blank row each time the values changes in two different columns. I’ve got a macro that sorts the data on two columns, just need to add rows after each change in both columns.
I've found something that'll add rows after each change in one column, the problem is adapting to add rows after each new value in a 2nd column as well.
What I have so far works until it hits the first empty row inserted after sorting by first column.
Sub SplitList()
'
' SplitList Macro
' Macro recorded 05/03/2004 by GaryB
rw = 5
myval = ActiveSheet.Cells(rw, 4).Value
[code].....
Here’s a data sample after performing my sorts, but before the macro to add spaces:
Another, Guy CDP EX 8.36 8.36 0 0 0 0 0
Jones, Some CDP SS 3.3 3.3 0 0 0 0 0
Justa, Nother ESP NV 22.45 22.45 0 0 0 0 0
Person, One ESP SS 23.36 23.36 0 0 0 0 0
Tom, Rich SSP MA 2.35 2.35 0 0 0 0 0
Dude, Steve SSP UN 63.86 25.36 5 1 2 1 1
Blanke, Spaece SSR MA 12.24 12.24 0 0 0 0 0
Try, Again SSR MA 15.25 15.25 0 0 0 0 0
And after the attempt to add rows: (note that it added a blank row between the first and 2nd rows (which differed in the 3rd column), but that it then stopped.
Another, Guy CDP EX 8.36 8.36 0 0 0 0 0
Jones, Some CDP SS 3.3 3.3 0 0 0 0 0
Justa, Nother ESP NV 22.45 22.45 0 0 0 0 0
Person, One ESP SS 23.36 23.36 0 0 0 0 0
[code].....
And, what I’d like it to look like:
Another, Guy CDP EX 8.36 8.36 0 0 0 0 0
Jones, Some CDP SS 3.3 3.3 0 0 0 0 0
Justa, Nother ESP NV 22.45 22.45 0 0 0 0 0
Person, One ESP SS 23.36 23.36 0 0 0 0 0
[code].....
You can see, it seems to be hanging up once it hits a blank row after first round of inserted rows.
I've tried using multiple loops in the forum but cannot seem to figure out how to actually get them to work properly using the conditional VBA codes on two separate worksheets. The first code snippet is checking cell values from row 6 to 148 as such:
Sub Check_Shifts()
'Insure all shift entries are completed
If Range("K6").Value < "1" And Range("I6").Value < "1" And Range("G6").Value < "1" Then
Range("G6").Value = Range("F6").Value
Range("I6").Value = Range("F6").Value
Range("K6").Value = Range("F6").Value
ElseIf Range("K6").Value < "1" And Range("I6").Value < "1" Then
Range("I6").Value = Range("G6").Value
Range("K6").Value = Range("G6").Value
ElseIf Range("K6").Value < "1" Then
Range("K6").Value = Range("I6").Value
End If
If Range("K7").Value < "1" And Range("I7").Value < "1" And Range("G7").Value < "1" Then........................
I have been currently working with Data in which involves time over 24 hours and I am trying to find the sum of these numbers.
An example would be the following :
4,889:38:33 +4,711:28:37 + 4,851:11:26 .
Of course, I have removed the comma and changed format, this did correct the issue. However, any way to find the sum with the comma still involved.
I want to loop through a range of cells changing the value of each cell by increments of 0.01 until a value of 10 is reached.
If the loop encounters a blank cell I want it to start the loop at M110 again
If the starting value of cell M117 is greater than 10 then I want the loop to use -0.01 increments to reach 10
If the starting value of cell M117 is less than 10 then I want the loop to use +0.01 increments to reach 10
This is what I have written so far...
Sub Test()
Dim i As Single
i = 0.01
Range("M110").Select
Do Until Range("M117").Value = 10
[Code] .......
I have this problem on putting the right formula. Column A entry are dates corresponding to replacement of parts x at column B with running hours of parts x at column c respectively. the table will look more like this:
A B C D
1 jan 2, 2008 brake front 200 ?
2 jan 20 , 2008 clutch 150
3 jan 30, 2008 wheel front left 300
4 feb 2, 2008 brake front 50
5 feb 5, 2008 brake left 85
6 feb 15, 2008 clutch 300
the formula will first look at column B and look for the entry "brake front" and every time it matches the entry it then looks up the date and then displays the corresponding latest running hour value on column C at cell D1. D1 formula will be having a result of 50 as it is the latest running hour value compared to 200.
Data file with few columns. There are groups of similar ID numbers in Column J. For a group of similar ID numbers in consecutive rows there is only one row that has a number greater than 0 in its Column L cell and the rest of the cells of Column L for that set of similar IDs is filled with 0s.
First for that unique ID group I need to find out which row is it that has a value greater than zero in its Column L cell.
Then I need to use that value to fill the rest of the 0s in Column L corresponding to that set of Unique IDs.
The process continues with identifying similar IDs in Column J and this time doing the same thing for their Column M. I have attached a sample file that shows the data and how the results need to look like.
See here Fill Cells.xlsx
I am trying to get a macro to run in excel that takes a simple text to columns command in one line of data and runs the command on a loop through however many rows of data there happen to be.
I've attached two screenshots - one with what I've got now (Before.jpg) and what I'd like to have after the macro runs (After.jpg). The code below is what I used to get the first text to column breakout, which I can hopefully run on a loop to breakout anything in the DEPT column that contains a "/". It can ignore the rows that only have one department to begin with.
Selection.TextToColumns Destination:=Range("K2"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="/", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Eventually I'll want to create another macro that transposes the breakout text back into the DEPT column and populates it with the corresponding data, but I figure I'll take things one step at a time.
Attached Images
Before.jpg‎
ter.jpg‎
I'm trying to get a macro together that will take a set of workbooks that I've merged (using Ron de Bruin's RDBMerge add-in) and transpose all columns from B to HB into rows. Now, I know that each spreadsheet is 210 columns and 244 rows large and they are concatenated on one another. Attached is a brief example of what I am trying to go from and what I am trying to get to.
View 1 Replies View RelatedI created a macro that analyzes some datasets and according to different parameters it puts an X at the side of every cell that goes out of parameters. These Xs are all in columns named the same way for it to be easier to find. What I want to do is to write some macro that will hide every row where the data did not go out of parameters to be able to easily see where it did. In the following example it would need to hide the second row since there are no Xs in that row. One of the problems I'm having is that the amount of columns and rows is variable.
Data
Data
Functional Upset
Data
Data
Functional Upset
#
#
X
#
#
[Code] .......
Below is the piece of code that I created to try to do this (G has the value for the amount of rows). I think it is working but it is either taking too long since it has to sometimes go through over 20k lines as much times as it needs to to cover all the rows that contain Xs or its getting stuck for some reason.
Code:
Worksheets(2).Range("I3:I" & G).EntireRow.Hidden = True
K = 0
Do Until Worksheets(2).Range("I1").Offset(0, K) = ""
If Worksheets(2).Range("I1").Offset(0, K) = "Functional Upset" Then
[Code] ......
I have the following macro which I use to transpose a number of columns in one sheet to rows in another sheet.
PHP
Sub task1()Dim i As Integer, n As LongFor i = 5 To Columns.Count Step 2 n = n + 1 With Sheets("Working_Checklist_1").Cells(10, i).Resize(16) Sheets("Summary_Intermediate").Cells(n + 2, "c").Resize(.Columns.Count, .Rows.Count) _ .FormulaArray = "=if(transpose(Working_Checklist_1!" & .Address & ")=0,"""",transpose(Working_Checklist_1!" & .Address & "))" End WithNextEnd Sub
I'd like to change this macro so to transpose from rows to columns. I've tried a couple of things, but can't quite get it to work.
I'd like to transpose every second cell starting from E7 to IV7 in Working_Checklist into column BU in Summary_Intermediate, starting from BU3.
I'm looking for a formula that would look up the individual employee (i.e. Name1 for example) in the January sheet and then look for and sum any amounts that have a number and a symbol as reflected in A2 through A10 (8V, 4Vâ–²,etc), which would then be calculated with a similar formula for the February sheet, specifically the time period between 1/26/14 and 02/08/14 as indicated above.
As employees can switch shifts, the formula would have to locate now in February where employee (Name1) is and find that row for the same symbol/number calculations for this same time period in that given month. Currently I'm using an array formula which can be seen in the attached file in cell I4. The problem is that the formula assumes that Name1 is always in the same spot in all monthly sheets which isn't the case month to month. The formula looks like this without the vlookup portion:
[Code] .....
I also need to do a similar vlookup or match, etc with this formula too:
[Code] .....
Attached File : sample payroll.xlsm‎
In A1, I've got a time for an event expressed in military time without colons, like 1130.
In B1, I've got two more times expressed the same way, like 1230-1300. These are the time the event was actually began and ended.
I've separated these two times into four different columns, the first two (C1 and D1) containing the raw numbers 1230 and 1330, and the second two (E1 and F1) containing those numbers formatted as times (12:30 and 13:00) via the TEXT function =TEXT(C1,"00:00")
Getting the difference between these two times (expressed in minutes) is not difficult. What is proving to be difficult (however simple it seems) is checking to see if C1 (time event began) is within an hour of A1 (time event was scheduled.) I was able to get this to work with times not involving 0000 or having, say, 0130 as an event time and 2300 as the time the event was started, but those two cases (switching 0000 to 2400, and moving between "days") is causing a hangup. Checking if A1
What I am trying to do is to look at specific columns, then copy the data in that column from specific rows from sheet 1 (named TIA) to sheet Macro1. I think uploading a sample of the spreadsheet would be useful.
Unfortunately the spreadsheet is a living document and continues to grow in both column and rows.. The data extracted at this point is from row 7, 23-60 and copied into the new worksheet starting at A1.
Requirements:
1) Row 5 states the macro the column will be associated with. There can be more that one macro associated to a column.
2) When column is found, data from row 7 column (x) will be copied to sheet macro1 EX. If Cell G5 = macro1 then copy data from G7 to sheet macro1 at A1
3) When column is found, data from row 23 column (x) will be copied to sheet macro1
4) repeat requirement 3 till no more Scenario's
I am using Excel 2010 and need a macro that can convert data from rows to columns. I have read several posts about this subject but have no experience with macros and don't know how to change the macros to fit my scenario.
Here is what I currently have:
Account...Vehicle1...Loc1...Vehicle2...Loc2...Vehicle3...Loc3...Vehicle4...Loc4
11111......2008........FL
11111......2000........FL
12121......1999........GA
33222......2000........AL
33222......2011........AL
33222......2001........MS
Here is what I need it to look like:
Account...Vehicle1...Loc1...Vehicle2...Loc2...Vehicle3...Loc3...Vehicle4...Loc4
11111......2008.......FL.......2000........FL
12121......1999.......GA
33222......2000.......AL.......2011........AL......2001.......MS
There are up to 4 vehicles/locations per account number, and I need 1 account number per row (the dots above are for spacing only and not part of the actual data).
I could do this manually but because I have so many rows of data it could take days or weeks. Is there a macro out there that can do this??
i have the following spreadsheet with dummy data however, there is a before and after scenario i have posted is this possible with a macro ...
View 9 Replies View Relatedi have created a spreadsheet to simplify our work flow, I am stuck on what is probably the easiest of the commands.
basically have rows dedicated to specific codes and the colums represent values relating to each code, all codes have a different set of values, the attached example only has a few variables but the actual worksheet will have several hundred.
the idea is the user will input the code they wish to get details on in A2 and then press the command button and it will then show (as per the after sheet in the attachment) just the relevant information for that code, so filter the code in column A and hide the columns which hold no value.
where i am getting stuck is I am not sure the best way to proceed, is it best to create the macro button to do the filter and hide or is there a better way using vlookup and a pop up window asking for the relevantcode to be inputted to to retrive the information, again understand there will be hundreds of colums and hundreds of rows and the values may be 20 or 30 colums apart for some of the Codes so this simplification is really saving the user a lot of time.
I need to make named ranges from an unknown number of columns(at least 1) each with an unknown number of rows. Each column has the name of the named range as the first row, and then a variable number of rows containing part numbers.
I can do it 1 by 1, but id rather do it in a loop so that blanks dont cause errors. there will be different people using versions of this sheet with different model/part number information What i've tried: Count number of colums with row 1 containing data (11 max, which is more than will ever be used) add into array(I know i dont really need to add into the array, but i might use it later for some other code). The problem i'm having is finding the range of rows that need added to the named dynamic range and adding it.
modelcount = Range("G7") 'G7 (for now) contains =COUNTA(H1,I1,J1,etc)
For i = 1 To modelcount
Redim Preserve Models(0 To i)
Models(i) = Cells(1, i + 7)
Range1 = Cells(2, i + 7).Address(xlA1)
lastRow = Cells(rows.Count, i + 7).End(xlUp).Row
Range2 = Cells(lastRow, i + 7).Address(xlA1)
Reference = Cells(2, i + 7).Address(xlA1)
ThisWorkbook.Names.Add Name:=Models(i), _
RefersTo:="=OFFSET(Reference,0,0,counta(Range1:Range2),1)", Visible:=True
Next i
This gets me the range i need, but doesnt create the named range properly. If i go to insert>names>define, the named ranges are created, but they dont relate to the data in any columns. It shows the variable names rather than the cell range the variable represents.