Value In Col 1 For First Non-zero Value In Cols 2-5?
Sep 3, 2012
I need to discern the first row where columns B or C or D or E become non-zero. This is because column A countains dates, and if I subtract one day from the date in the non-zero row I have the date of the analysis I'm examining.
The problem i have is that the code I've put together below gives me the date of the absolute last row in the spreadsheet, "31/12/2015 23:30:00" from row 70129 which isn't that useful. I'm expecting a date in August just gone which is on row 11618.
Code:
Sub test()
Dim lngLastRowU1 As Long
Dim lngLastRowU2 As Long
Dim lngLastRowU3 As Long
i want to do the same thing with the formula below, but for columns (i want to use the value in cell cw106 on sheet "POINTS" as the upper limit & cw107 as the lower limit.
Columns("aE:aF").EntireColumn.Hidden = True
i tired it like this and it wouldnt work Columns(Sheets("POINTS").Range("cw104") & ":" & Sheets("POINTS").Range("cw105")).EntireColumn.Hidden
I need to automate the attached as per the following (by not recording macro unfortunately).
Sheet one has a dump of data. First, I need to change the data format from yyyymmdd to dd/mm/yyyy. Then I need to filter for region EU and only copy the filtered data from columns A, C-D, F and the difference from columns I-J to Sheet 2. Then in Sheet 2, I need another column to calculate the difference per unit.
My excel file was imported from a 3rd party and has many blank rows across the entire sheet. I want to delete these rows if every cell across is blank. How is this accomplished?
I tried the formula "=isblank(C2:C18)" however I get a "False response on those rows that are completly blank.
i have data that will vary from item to item in the $ of rows associated and the # of columns to be dealt with.
item1 Line 1 Line 2 Here i want to have a formula put and copied across X columns item2 Line 1 Line 2 other data going across in the columns Line 3 Line 4 Here i want to have a formula put and copied across X columns item3 Line 1 item4 Line 1
get my meaning??
columns involved will be known before i get to this bit of code !! as an example lets say it's 8 columns
using the record macro i get this bit of code
Range("E14:M14").Select Selection.FormulaR1C1 = "=SUM(R[-10]C:R[-1]C)" This is fine but i don't know what row or the columns are before i run the macro so the Range ("e14:M14").select wont really work.
I've sent sometime going through MRexcell searching for tips but found nothing also googled for dynamically pasting across unknown columns
this is close to my idea in my mind conceptually Range("C2:C" & LastRow) but it doesn't make the columns dynamic eg column 8 being = H and then P = 8th column on and the last with data.
From the above thread I am using the following macro...
Sub MoveOver() Dim MoveRange As Range
Application.ScreenUpdating = False With Range("B1", Range("B" & Rows.Count).End(xlUp).Offset(, 21)) .AutoFilter field:=1, Criteria1:="=C&P" On Error Resume Next Set MoveRange = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible) On Error GoTo 0 .AutoFilter End With If Not MoveRange Is Nothing Then MoveRange.Insert Shift:=xlToRight End If Application.ScreenUpdating = True End Sub
I am getting an error 1004 message saying cells cannot be moved off of the spreadsheet. However, my last cell on the sheet is J28956.
Columns B through J contain data. Columns beyond J do not contain data.
Can someone give advice on getting the macro to work.
I know there have been many posts about multiple conditions in arrays, but I didn't find one that applied to my situation, so I'm hoping can help me out of jam. I'm using Excel 2000 on XP.
I have a database of sales information. Customer name is listed in column C, and columns F through Q contain the sales for the months of July - June (fiscal calendar year), with the headers for the months in row 4. There can be many rows of sales for the same customer, hence the need to sum them.
Example
C F G H CUSTOMER JULY AUGUST SEPTEMBER customer1 2 3 4 customer1 2 3 4 customer2 2 3 4 customer2 2 3 4
The problem is that this information will need to be updated every month to reflect sales year-to-date, meaning that the conditions for summing the data will change depending on how far into the year we are. So for instance, in August I will need to sum all the records for customer1 in the July column (column F) and the August column (column G), but next month it will have to sum the records for customer1 from July, August, and September (col H).
The result of this information would be displayed on a different worksheet. My thought was to assign a range of 12 cells (Sheet2!A1:A12) and to populate those cells with any of the months that need to be summed thus far. So for example, through September, A1 = "July", A2 = "August", and A3 = "September". Then the formula would use those values to evaulate the conditions. So in plain English the formula would:
sum the values in columns F : Q where the monthly header = the values in the range Sheet2!A1:A12 for all records where CUSTOMER = CustomerName
I have 40000 rows of data to check. I always filter Column E by Suppplier Name, like supplier Alpha or Bravo,Charlie,Donkey, or Elephant,France,Goal etc.
What I manually check is I filter the column E by Alpha, then I will get some 5000 rows of data of Alpha, I will look if all the rows in Column B contais "-3digit" numbers like -123,-548 etc if not I will assume its incorrect and copy the entire row and put it in next sheet.
If Column B is right that is, if it contains -3digit then I will again look in Column F ( Invoice Numbers) the invoice number should always be 5digt-4digit all numbers without space or any special charactors except Hypen(-). It may have 5digt-4digit-5digit-4digit etc, if any values doesnt match with the said criteria i will again assume it as incorrect and move to the next sheet.
Now once this is done i will filter againn Column E by "Bravo" all the conditions are same, that is if the supplier name is Alpha Bravo, charlie, Donkey, or Elephant then i will do all the checks mentioned above or else i will ignore all other supplier....
I have an issue with an export file which is produced in CSV format and needs to be aligned. I have attached a sample of what I refer to.
My Source excel file looks like:
ABC A-101 B - 202 DEF B-203 C - 100
The destination should align with the relevant column headers and place a blank if it doesn't match. Other have queried a similar thing, however my header and data and description is actually in the same cell with a dash separating them.
The output I want is:
A B C D ABC 101 202 DEF 203 100
There are multiple row of employees with varied column headings as its dependent on what system access the user has. The headings i.e. A,B,C for example is a fixed number of headers.
In my real data set the headers represents a 3 letter system prefix e.g. ZCR,ILP etc
I am merging columns. Col B has all text in bold. Col C has all text in regular font. When I use ASAP to merge, the merged column comes out all bold. When I use a formula to merge the merged column comes out all regular. What I want is for the merged column to contain the bold text from Col B in bold, and the regular text from Col C in regular.
What I want is...
This is for service plan B: Take all data and transer to new server.
What I am getting is...
This is for service plan B:Take all data and transer to new server.
OR
This is for service plan B:Take all data and transer to new server.
