way I can use relative cell address in VBA? For example, in the following table:
value item 1 apple item 2 peach item 3 mango
Instead of use
Range("B4").Value = "mango"
to insert mango to cell B4, I can use an address that's one row under peach, or one column to the right of item 3?
In cell C2, I have =SUM(cellsBeforeUp), where the named range cellsBeforeUp refers to =Sheet1!$A1:B1
The idea is to have a named range that refer to the cells in the preceding row but to the left of the current column, via relative addressing in the name's definition.
The problem is that the formula returns 0 (which is wrong). (changing RefersTo to =Sheet1$A1:$B1 gets the correct sum, but the range does not expand when dragged as =SUM($A1:B1) would.)
In Cell D2, the formula =SUM(cellsBeforeUpA) where cellsBeforeUpA refers to =OFFSET(Sheet1!$A1:C1,0,0)
This name works properly.
I don't understand why OFFSETing by 0,0 is required to make this work properly.
A few days ago I had a list of values in column A, and wanted to display in column B, in the cell where the cursor is when the macro is started (let's call it Bn), the sum of values (An:An-11), where n can be any row where there are values to be added.
"XLD" showed me how to clear the values from the column where the cursor is when the macro is started, and how to display in that cell the sum of 12 values in the adjacent column.
Assume the following list of addresses are all in separate cells of a single column (A1-A4). I just need the formula to extract the street addresses, and then a separate formula to extract the zip codes.
5430-44 PASCHALL AVENUE PHILADELPHIA, PA 19143 OPA/BRT#: 884350845
4010 MARPLE STREET PHILADELPHIA, PA 19136 OPA/BRT#: 651087200
2618 SOUTH HOWARD STREET PHILADELPHIA, PA 19148 OPA/BRT#: 391251216
5737 WOODCREST AVENUE PHILADELPHIA, PA 19131 OPA/BRT#: 522155600
Below is my code which isn't formatting the cells it's suppossed to. It looks like it isn't doing anything. I think the issue might be with the highlighted section of my code, but when I go to "Manage" my rules for conditional formatting, excel references the appropriate cell under the "Applies to" section. I am using relative cell references for for the majority of the rest of the code and this section follows a section that selects the correct cell for this conditional format.
Code:
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=""ABS()>.005"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority[code].....
The problem I am trying to solve is to have a cell return a cell value based on another linked cell. The cells I want to link are in separate tabs or could be another workbook.
For example if cell A5 in sheet2 is linked to A20 in sheet1...A5 has formula(=Sheet1!A20). I want the next cell B5 to look at A5 and return the value of cell Sheet1!D18.
So in essence I want the second cell to look at the first cell and return a value that is 3 columns over and 2 rows up.
I'm importing comma delimited files into Excel. These files will contain formulas, but do not know on which row they will end up in once imported. My formula's need to reference the row they have been pasted to. For example, If I paste the formula on Row 2 the formula would be =A2+B2
Row 3 =A3+B3
However, since these formulas are coming in via a non-excel file, I need the cell reference to be dynamic. Something along the lines of =A#+B# where # would be interpreted by Excel as the current row the formula is on. (I know that '#' isn't correct.)
I have recorded macros in the attached file to highlight cells that contain matching initials by using conditional formatting on the first cell and copying it to the remaining cells. The problem is that if rows are inserted above that change the first cell reference then the macro no longer works properly. Is there a way to use a relative cell reference within the macro? The macro buttons are in cells A4 to A13. I have no VB experience and created the macros by recording keystrokes and using tips found in this forum.
View 14 Replies View RelatedI currently have a list of totals that i want to easily be able to change the cells that make up this total.
I have been trying to set up one cell that contains the formula and then the remaining totals below are linked to this cell. Thus I change the formula in the top cell all the other totals also change.
I seem to be having problems with using relative RC in vba within an automated reporting spreadsheet..
in my 'left lookup' below.. the R seems to point to row 1 no matter what row i am writing to..
I'm working on my first real macro - so I'm a greenhorn. I've spent a few hours trying to research a solution, but most threads are too technical for me so I'm hoping someone can really help me out.
I am trying to use a formula that references a cell that changes day to day from when I use the macro. I need to use a formula which grabs from a cell in a non concrete location. Let me try and show you what I mean.
From I4:I10, I need a formula like this:
I need to drag a cell using a macro and using relative reference.
i.e. drag activecell to activecell.offset(1,-1)
I have this forumula in a cell:
=SUM((COUNTIF(F7:F69,"<=" & TODAY())),(COUNTIF(B7:B69,">=" & D7)))
and the trouble I am having is that in the second Countif, I need it to check the cell in the "D" column relative to what cell is being checked in the "B" column. Right now it is checking every cell in "B" against "D7" instead of "D8, D9, D10", according to what cell in "B" is selected.
This is one of about 25 sheets in one excel file, and I will be receiving about 5 different files a week, so a macro is necessary to save time. I tried to create a macro just by recording, and it was not able to adjust to the variances in the number of lines between the sheets.
I need it to do the following, and more or less in this order:
-Delete Column A
-Create text and format A1:C1
-Sort rows 2 through the last row based on column C values (from largest to smallest, although its not shown in the picture above)
-Create a sum at the bottom
-Apply grid lines to all the data
I don't have a problem with the first two, but I cannot figure out how to adjust for the number of rows for sorting, offset 1 cell down to create the sum formula below the data, or how to properly write the macro for the sum formula itself. When I recorded the macro, it used cell references like Range("C5").Select and others which do not adapt to the varying number of rows.
I tried using the offset command in this thread without much luck.
This is some of the macro I have:
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select
ActiveCell.FormulaR1C1 = "Customer ID"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Customer Name"........
I am in the middle of my ICT A-level coursework and im required to design a spreadsheet with a grade system (A to F). The students have individual target grades and recorded grades from tests and homework will also be dispalyed. How do i change the colour of the cells of tests or homework depending on what target grade the student has. I will have to do this for about a hundred students, so conditional formatting isn't really an option to do each individual cell. I'm not familiar with coding, but can learn. Not sure if a formula can solve this either.
I have used the search function of the forum for this question but have not found anything regararding this.
I have a list of University courses listed in Sheet1. Column A contains a unique course code. If there is a problem with a course, a second worksheet (called Anomalies) contains the same unique course code with description of the problem(s).
What I'm trying to do is: in Sheet 1, create a hyperlink to the relative cell in Sheet 2 that shows the course code and description. I've gotten close, but am not sure how I can do this without having to update two formulas. In short, I can't find a way to combine the two formulas.
What I've done is the following: in Sheet 1, column B, created a HYPERLINK formula, thus: =HYPERLINK("#"&CELL("address",INDIRECT(C54)),A54)
Column C contains a formula to lookup the relative position in the Anomalies worksheet, which can be referenced back to the HYPERLINK formula, thus:
=("Anomalies!A"&MATCH(A54,Anomalies!A:A,0))
Sheet1 is fairly static, but Sheet2 (Anomalies) is being updated all the time. I could just hide column C and then column B will update based on that, but I'd prefer a cleaner solution.
I've tried joining the two formulas, by replacing the INDIRECT part with the second formula, as per the following: HYPERLINK("#"&CELL("address","Anomalies!A"&MATCH(A54,Anomalies!A:A,0)),A54)
but I get an error.
I have 8 columns of data, the first 4 and the last four columns have the values which correspond to each other.. Here is an example:
VALUES(A to D)NAMES(A to D)
ABCDABCD
1.3451641.3383581.286221.359808321325370391
So value 1.345164 corresponds to 321 and 1.28622 to 370. I need a function that will return a value from one of the VALUES columns which corresponds to the second smallest cell value from the NAMES columns. For example – here the second smallest number in NAMES column is 325 so the function would return the corresponding value of 1.338358.
Im looking for code for a button that asks for a Record number, and it will delete the record. But the record number is written in cells as 'Record No.1' 'Record No.2' etc. But i want the messagebox to ask for a single number and it would delete that record. Now the position of the records is what will be the difficult bit about this code, but it has a pattern.
For Record No.1 i need deleting cells C2:J100
For Record No.2 i need deleting cells L2:S100
For Record No.3 i need deleting cells U2:AB100
If anyone could give me a bit of code that works for these 3 then i could waste a load of time filling the rest of them out.
I am using VB to insert a function into a cell on an excell worksheet. The relative references work fine. For the absolute reference, I want to use a named range (LowTotal). If I simply put the name into the function, then I just get the name as text in the function. The second insert formula is were the named range is intended to go. Here is the code so far...
I need to know how to use the varriable name in the .formulaR1C1, so that VB knows it is a variable and not just text....
I am struggling to find a macro which can look at a name in column 'BT' and search it in the address book of Outlook to then place the email address of that person in column 'ED'
There are 35,000+ people in the address book and there may be over 5 email addresses for one name, so is there any way a message can appear for the user to select which email address is correct if there is more than 1 contact for that name?
In column A I have a set of Ids that are not unique. In column B I have a set of latlong values for those Ids in column A. In column D I have the unique list of Ids from column A(Removed duplicates from column A & pasted them in column D). In column E, I need the latlong value which has highest frequency for each Id. I tried countif,Frequency and tried mixing them up with various other formulas but it is out of my reach.
View 3 Replies View RelatedHow to copy an array to an array of the same size without changing relative
cell references in the formulas?
I need to insert a picture on a Excel 2010 worksheet.
The worksheet contains a variable number of columns, and the column width is also variable.
The picture needs to be aligned such that the right edge of the picture is in line with the right side of the last used column.
This is so that when the worksheet is converted to a PDF document the picture (company logo) appears on the top right hand side of the page.
I can find the cell reference for the last column, and I can move the picture around the page. But I'm not sure how to find the position of the last column on the page?
I've got this far:
'copy picture and resize and position
Sheets("Output File").Select
ActiveSheet.Shapes.Range(Array("Picture 1")).Select
Sheets("External data sheet").Select
Cells(1, 10).Select ' I can make this be relative to the last column on the WS
ActiveSheet.Paste
Selection.ShapeRange.Height = 50
Selection.ShapeRange.IncrementLeft 0 'put the picture in a position relative to the cell selected
I would like to select a range of cells relative to the current cell and move them to the right two cells. Basically, if I were in cell A1 I want to be able to have a macro select A1 - A8 and move them over by two cells leaving A1 and A2 blank.
Excel 2003
I need to change multiple conditional formats which apply to cell ranges 1 row x 17 columns wide. The specific change I am making is able to be acrried out with the code
With Selection.FormatConditions(1).Font
.ColorIndex = xlAutomatic
End With
However, I dont want the user to have to manually select each 17 cell area. I WANT THE USER TO JUST MANUALLY SELECT THE FIRST OF THE 17 CELL AREA. I want to write a macro that will THEN select a row of cells, 17 cells long, from the MANUALLY SELECTED position as at the moment of running the macro. Unfortunately the cells are not always on the same row in each sheet. On one sheet they may be on row 5, on another row 8 and on another, row 15 etc. I was wanting to be able to select a 17 cell range 'relative' to where the cursor is. How do I write the code to select the relative cell range? Is there an answer in an R[1]C[1]:R[1]C[17] sort of code?
I want to find the subtotal amount on one worksheet, which is two columns over from the text, "Sub Total:" on the same row. The subtotal line floats up and down each week. Once I can find the subtotal I want to populate another worksheet with the data.
View 5 Replies View RelatedMy sales spreadsheets have a column which is turned to 100% when the order comes in (i.e. when the salesman gets it in his commission). I have applied conditional formatting to turn the whole row green when this happens (for ease of seeing which orders are in)
We also have a cell for the Purchase Order "number" (as these come from the customer, these can be a straight number sequence - "12345" or a mix of letters and numbers - "ABC12345"). My boss would like this cell to be red if the "order in" column is at 100%, but there is no PO number.
However, when I put in the formatting:
(1) =$K$15=1 (to turn the row green)
(2) Cell Value is equal to 0 (to turn the cell red)
it will apply (1) no problem, but will only apply (2) if (1) is false. (i.e. if (1) is true, the whole row goes green, with no red in the PO box.)
If I switch them round (i.e. have (2) first), then I get the formatting that I want (i.e. green row with a red box), but if (1) is false, I still get a red PO box.
What I need is a way of only applying (2) if (1) is true. (or any other way of only making the PO box red if the order is 100% but there is no PO number)
I have a list for addresses in excel in single column as shown below - aanandhi narayanan 3430 chemin de riviere sanjose,CA95148
DOMINIC ABANO 3838 GLENGROVE WAY SAN JOSE, CA 95121
abdi abdi 5390 monterey rd #6 sanjose,CA95111
Sheribel Abinsay 3212 Gateland CT San Jose, Ca 95148
I need the result to be in a way like -
3430 Chemin de riviere
San Jose
CA
95148
3838 Glengrove way
San Jose
CA
95121
5390 monterey rd#6
San Jose
CA
95111
3212 Gateland CT
San Jose
CA
95148
I have around 12000 records with the same format.
On a worksheet called "Contact Info" column A starting in row 2 I have a list of names (variable length). In Columns B2-D I need the email address, work phone number, and cell phone number.
View 5 Replies View RelatedI have a project coming up where I will receive tables in a certain format. I want to be able to create a macro that well create a chart based on a single inputted cell (start of data). The chart will have 4 series and five values per series. See the link below for sample data! The first data point is not in the same place in the sheet, but the table will have the rest of the data relative to that!
[URL] .......