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.
Does excel have a formula to check to see if every row in a specific column is blank and if it is then set the value?
View 10 Replies View RelatedI'm trying create a macro to enter a series of forumula's in a series of rows in whatever column is currently selected (or column which has a cell selected). IE if the active cell is C5 I want "=A1+B1" copied to C10 of it was AA43 selected I'd want "=A1+B1" copied to AA10. Have done this with setting a row as a variable, but whenever I've defined the column as one it comes out as a numeric value. and gives me "method range of object global failed"
View 3 Replies View RelatedI need a macro to move specific information.
I get an excel sheet with W/C Employer: (whatever) entered below the patient it belongs to in column E. This information needs to move into its own column (H) in line with the above patient information so I can use it in a mail merge to word.
The same thing needs to happen with WC injury date listed in column G. It moves to column I. Then delete the row it was moved from so I don't have an empty row.
Sheet 1 is what I get. Sheet 2 is what I need to end up with.
I want to insert a formula that is tied to a specific column. I know how to do formulas and have a slight understanding of macros. Can I insert the formula as part of a larger macro?
View 3 Replies View RelatedI need a macro, or a formula that can identify if the words in the Words Column (Column A) is contained in Title Column (Column B). If it is, It displays as "Yes". If not, display as "No".
Case is not sensitive.
Words
Title
Displayed?
Christmas
Coworker has chronic hiccups
Yes
Excel
I Love Excel
Yes
Cartman
I Like Turtles
No
Ninja
Oh Christmas Tree
Yes
Tiger
Case of the Mondays
No
Chronic
Cute Monkeys
No
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.
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 need the macro to look at cells B9:B84 on the Sheet1 tab of the Cost Template. If it finds an x I need it to copy the 3 cells to the right of the x and paste them in a template. For example if it sees an x in cell B9 it would copy cells C9, D9 and E9, open the Purchase Order to the Detail tab, then paste it to cells B3, C3 and D3. It would continue looking for an x down to B84. So if it found 5 cells with x, it would give me 5 instances of the Purchase Order with 3 cells pasted into each.
I've attached my Cost Template and the Purchase Order it needs to copy to. In the Cost Template is a macro called Create_PO. This is what I was trying to alter to make this happen. I can't seem to get it right! FYI in case it matters, I had to change the Cost Template from .xltm to .xlsm in order to upload it on this site.
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
Notice the two examples in the excel file.
Excelforum.xlsx
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):
Find_Range("A", Columns("J"), MatchCase:=True).EntireRow.Delete
Find_Range("B", Columns("J"), MatchCase:=True).EntireRow.Delete
Find_Range("C", Columns("J"), MatchCase:=True).EntireRow.Delete
Find_Range("D", Columns("J"), MatchCase:=True).EntireRow.Delete
Find_Range("E", Columns("J"), MatchCase:=True).EntireRow.Delete
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 need a code that will delete a row if it has a specific text in a specific column.
In this case in column W if there is anything that starts with '>TC' I need that entire row deleted that its in.
What I'd like to do is; If column C contains data then insert a blank column and shift column C to the right.
View 4 Replies View Relatedlet say in cell A6 i have a list
SnG
Cash
MTT
I can pick one of these. If i pick Cash then i want a specific cell to run a specific formula. depending on the item chosen.
So in cell A6 is the list.
in cell B6 an amount will be place and in C6 another amount will be place. the number for c6 will range from a -number to a +number.
But D6 looks to see if B6 meet the requirements for MTT, SnG or Cash depending on which item was selected from the list. and uses the formula below with $c$3 changing from to either $D$3 or $F$3 and comparing the values for the chosen item. the formula changes would look this
=IF(ISBLANK(C6),IF(B6>$C$3,"Do Not Play","PLAY"),IF(C6<B6, "Lost","Win"))
=IF(ISBLANK(C6),IF(B6>$D$3,"Do Not Play","PLAY"),IF(C6<B6, "Lost","Win"))
=IF(ISBLANK(C6),IF(B6>$F$3,"Do Not Play","PLAY"),IF(C6<B6, "Lost","Win"))
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.
In the attachment is a file, which allows me to specific a Column and hide. For Example enter "H" in the InputBox and Submit the Column "H" is hide.
What do i have to change in the code if i want to hide the "H" and the Column two columns next to "H". In this case "J".
HideColumn.xlsm
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.
View 2 Replies View RelatedI'm trying to find a way to get the value of the last row, column F. I've tried
Code:
lr = Cells(Rows.Count, "F").End(xlUp).Row vrmiscur = lr.Value
but get an object required error on
Code:
vrmiscur = lr.Value
I am trying to sum specific cells withing 1 column based on a character within another column. For instance:
A1 = X and H1=1000
A3 = null and H3 = 50
A5 = x and H5 = 100
I want to sum everything in Column H where column A only contains an "x". In this scenario the sum would be 1100.
if I wan to get the value of specific column, how should I use it by vlookup?
The formula for vlookup is like is =vlookup(lookup value,table array, col_index,range lookup)
If the col index range, can i specific any column, example of I lookupvalue from A, then get value from CH?
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.
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.
refine this piece of code so it only looks in column 2 for a matching value.
On Error Resume Next
If S = "" Then
MsgBox "Delete Cancelled"
Else
Set r = .Columns(2). Find(What:=S, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
On Error Goto 0
If Not r Is Nothing Then Application.Goto r, True
If r Is Nothing Then
MsgBox "That APL ref does not exist"
Else
Dim rowNum As Long: Rem *** *****
rowNum = r.Row: Rem *** *****
Range(Cells(r.Row, "A"), Cells(r.Row, "AH")).Select
Selection.Delete Shift:=xlUp
Have a set of dates as column headers across a worksheet. Rows are product names. When a product is delivered, the cell is marked "delivered" for the date (in the column header) it was delivered. Need a formula that will look across each row (product), and return the date of the first cell with the value "delivered". I have: =INDEX(F19:AS19,1,MATCH("delivered",F19:AS19,0)) <for row 19. right now, but its just giving me "delivered" as the value and I cant figure out why.
View 2 Replies View RelatedIn my worksheet I have a column in which the cells have two possible values: "yes" or "no". In the first positions of the column there are only "yes" values, after there are some "no" values and then the "yes" values come again. Is there any function that identificates the position of the first "no" value in the column? And also the last "no" value? Attached I send a simple excel worksheet
View 2 Replies View RelatedI'm trying to make a spreadsheet for work so when im not there they can just enter the day for example sunday and what they have and then it will show them what they need to prep
View 4 Replies View RelatedI have the following formula...
=SUMPRODUCT((C:C="A CO")*(T:T="Pay Hurt")).
It shows the number of cells in column C say "A CO" AND the number of cells in column T show "Pay Hurt". I had this formula on the same tab as the data, and then a Report Tab that pulls that total number.
I only need that total number on the Report Tab but don't know how to put that formula in that tab while referencing the data from a different tab. Is there a way to have that formula in the Report Tab that references the data in the "Data" tab. My only guess would be something like
=Data!SUMPRODUCT((C:C="A CO")*(T:T="Pay Hurt"))
But this didn't work.
Hello, i've been using the below formula to find the most consecutive "W" (Wins) without any "L" (Loses) in 2 rows, there are between the "W" blanks and and numbers
=MAX(FREQUENCY(IF($Y5:$DP5="W",COLUMN($Y5:$DP5)),IF($Y5:$DP5"",IF($Y5:$DP5"W",IF($Y5:$DP5"",IF($Y5:$DP5>0,COLUMN($Y5:$DP5)))))))
The problem is at the start or end of the count eg
A B C D
L WWW
W W L
The above returns 5 the result i am looking for is 3 as column B and C are the only ones without an L, there are alot more columns so obviously if there are larger sets of consecutive W then the result would be bigger,
I have a UDF which basically accomplishes a lookup. To do that I use this
Function name(number As String)
Select Case number
Case Is = "1"
name = "BOB"
.
.
.