Create Mixed References In Macros
Feb 9, 2010
I am trying to use a macro to copy a set of cells.
I have successfuly created a macro - the problem is I would like the column reference to be absolute but the row reference to be relative. I can have one or the other but not mixed it seems.
If I have not explained myself well, perhaps this may help - I need to copy cells $J1:$S1 and paste them to $L1:$U1
Currently the macros works if the cursor is in cell J1 when I activate the macro but if I move the cursor to any other cell it copies and pastes the wrong set of cells.
View 9 Replies
ADVERTISEMENT
Jun 17, 2006
I've read several threads about switching between relative, absolute, and
mixed references across several cells however these solutions seem to result
in formula with all relative or all absolute or all mixed.
I need to change the formula in lots of cells with a mix of types of
reference. e.g. I need to change "$E$4*AD$2" to "$E4*$AD$2" & would prefer
not to have to go though each of the cells with F4!
View 11 Replies
View Related
Jan 3, 2008
I am linking a row of data in worksheet x to a column of data in worksheet Y and would like to be able to drag down the cell reference in worksheet Y in this way:
=MIN(RuleofOriginData!AR$4:AR$63)
=MIN(RuleofOriginData!AS$4:AS$63)
=MIN(RuleofOriginData!AT$4:AT$63)
However, when I drag it down, the formula is automatically absolute for both the row and the column number instead of just the row as I indicated. Can anyone tell me why this occurs and whether there is a way to fix it?
View 9 Replies
View Related
Jan 6, 2012
Ok, so here's the trick:
I know how to create combo or mixed graphs with line/bars on single axis, and secondary axis. I even know how to do mixed stacked and unstacked columnar charts...
But how do you depict in single view graph
a single bar (1 datapoint) with a single line (upper control limit e.g. target).?
And I don't want to to use shapes to draw the target line.
I want the target line to be automatically plotted by Excel...
View 9 Replies
View Related
Jul 31, 2006
can't seem to understand how to keep cells absolute when creating a macro
View 8 Replies
View Related
Oct 25, 2007
I have a workbook within which i have a worksheet that contains a lots of macro code (coded by me). As the workbook gets used by various people, i need to copy the worksheet and the macros across to the updated workbook, which doesnt contain the macro worksheet at all.
I have tried to copy it across by clicking on its tab and using the move or copy facility. This copies the sheet across as required. But for some reason, the macros all reference the old workbook. A small bit here for example for some reason opens up the old workbook and then performs the code in the old worksheet:
Sub SelectAll()
For i = 12 To 20
Set curcell = Worksheets("Form Generator").Cells(i, 3)
If curcell = False Then
Cells(i, 3).Value = True
End If
Next i
End Sub
As curcell is equal to worksheets...() i would have expected it to use the local worksheet, ie the one that the macro is attached to. So why is excel proactively hunting out the old workbook and sheet? is the method i used to copy across the sheet with the macros incorrect? If so, how should i go about it?
View 3 Replies
View Related
Jan 19, 2012
I am not sure if this is even possible, but it would make my life a lot easier if it was.
Currently, I have a formula that goes like this
=IF('SAP DUMP'!A4="","",LEFT(RIGHT('SAP DUMP'!A4,9),8))
What I am asking is, is there anyway to replace the A4 (so the cell reference in SAP DUMP) with a formulae that looks at other cells
For example, I know that every 39 rows is the information I require. So is there anyway to CONC("A"&I4), with I4 being the row reference in the actual formula?
In my mind, it would look something like this
=IF('SAP DUMP'!("A"&I4)="","",LEFT(RIGHT('SAP DUMP'!("A"&I4),9),8))
However, when I try that, it doesn't work.
View 2 Replies
View Related
May 18, 2007
I have an API that continuously gets stock price. The price appears in one cell at all times - let's call it A1
I want to record the value in a different cell every time it changes, so I can analyze the data.
so A1 = 15
B1 = 15
little later
A1 = 15.2
B2 = 15.2
I did some googling and came to a conclusion that I needed to write a macro.
I see that there is a function or event called Worksheet_Calculate()
Apparently it is supposed to do whatever when a value changes (per calculation)
How do I write a macro, and then execute it?
View 10 Replies
View Related
Nov 6, 2009
I have a couple spreadsheets which update using a RTD link. I then use Macros to sort it and publish a html file every 30 seconds.
I found the html macro thanks to : http://www.meadinkent.co.uk/xlhtmltable.htm
The problem is that whenever I run two sheets together, the macro that creates the html file at times gets the data off the wrong worksheet. Sometimes I only see one sheet's data being used, sometimes the other.
Both html files when published have some feature from one macro and some from the other macro. Like macro one sheet says use 1528 rows and the title for page is Relative Strength Over 750K. Other macro says use 979 rows and title for page is Relative Strength Under 750K. I would have both the sheets as like 1528 rows for with title Relative Strength Under 750K.
If I run one sheet at a time, everything works fine.
Here are the Macro codes for Sheet #1:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime dTime, "Macro1", , False
End Sub
Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("00:00:30"), "Macro1"
End Sub
Module 1
Public dTime As Date
Sub Macro1()
dTime = Now + TimeValue("00:00:30")
Application.OnTime dTime, "Macro1"
Columns("P:AH").Select
Selection.Sort Key1:=Range("AG1"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Application.ScreenUpdating = True........................................................
View 12 Replies
View Related
Feb 11, 2010
I have 2 workbookts: 1) Product; and 2) Buyers.
1) In the Products worksheet, every column has data about the books' description like:
A | B | C | D | E
Serial | Desc. | Author | Pubd. | year
Column A contains serial no. of the books, ordered from 1,2,3,4,5,6,7 to .....so on...
Columns B,C,D and E, have other details of the books.
2) In the Buyers worksheet, there are two columns;
A | B
Serial | Buyer Info
These are the details people who bought the particular book order by the serial no. of the book purchased.
Required
I need to make a new column in the first worksheet i.e. Product, say F, which I want to read all the buyers' info of that purchase the book with that serial no. and concatenate them together in the same cell separated by a comma.
View 3 Replies
View Related
Feb 18, 2013
I have 6 macros recorded and I need to create a drop down list of macros so that I know what kind of Macro I am using.
View 9 Replies
View Related
May 21, 2014
So I'm trying my hand at creating VBA buttons and functions that do the following.
Button 1 :
to add a column with every click. The button will create one extra column. So by design there will be at least 1 column. Example if the button is clicked twice it will create 3 columns. I just need a buffer col. (contains no data)
Button 2 :
to delete empty columns leaving only one empty buffer column. That is this button will purge empty columns except the buffer column. OR if ALL the columns are filled, Button2 will create the buffer column.
Eg. I click Button 1 three times. It creates four columns. I only fill two columns. When Button 2 is clicked it will delete all the empty colums except the buffer column. OR I fill in all four columns, there is no buffer column, when I click Button2 it will insert the buffer col.
Button 3 :
to add a row with every click. The button will create one extra row. So by design there will be at least 1 row. Example if the button is clicked twice it will create 3 rows. I just need a buffer row. (contains no data)
Button 4 :
to delete empty row leaving only one empty buffer row. That is this button will purge empty row except the buffer row. OR if ALL the columns are filled, Button4 will create the buffer column.
Eg. I click Button 3 three times. It creates four rows. I only fill two rows. When Button 4 is clicked it will delete all the empty rows except the buffer rows. OR I fill in all four rows, there is no buffer rows, when I click Button4 it will insert the buffer row.
In the sample file there are six columns filled with data. The 7th col is empty(the buffer column). The column after that calculates the average of data in filled columns. There are eight rows with data and the ninth row is the empty buffer row.
The "add" buttons must be able copy the format/formula of the column/rows BEFORE the buffer colums/rows and insert it before the buffer column/row.
View 3 Replies
View Related
Mar 26, 2014
Is it possible to use design mode to create a button for multiples macros?
What i want to do is create like a list, and every selection run a different macro. I have a report that i want to use macro to make different filter selections, it would be about 20 different filters combination, so 20 buttons would be a mess... Is that possible or can i do it in another way?
And is possible to link the filter value to a different cell?
View 2 Replies
View Related
Nov 7, 2012
"How to create a 'main' macro to control other macros within a workbook". I have my individual macros created. There's one macro for each sheet that searches online data and returns the information. I have one of these per sheet (a total of 20 sheets) since I couldn't find a way to have all 20 macros be on one sheet and still work. My trouble is that whenever I'm on my 'main' sheet and try to run the macro which applies to a 'secondary' sheet, I get an error. I have to select the sheet first, then run the macro and it works. Below is the macro on each sheet.
VB:
Sub Holding1()
Dim DataSheet As Worksheet
Dim EndDate As Date [code].....
View 8 Replies
View Related
Jul 9, 2014
I have created a individual tracker spread sheet in each user system and on the spread sheet I have inserted 2 buttons in 1st sheet. when employee wants to take a break he has to click on the log out button and when he came back he needs to click on login button. I have another sheet named "timings" and I have created 2 columns, one is login and another is logout. What I need is when user click on the login button, the system time and date to be auto update under login column and when he clicks on logout button the system time and date to be auto update under logout column.
View 14 Replies
View Related
Feb 6, 2014
I have a calculator and I am trying to set a macro that will take the cells highlighted copy them to another sheet, change the format to standard( no background color) make the data fit in one page and print it.
this is what I got so far:
Sub Set_Print()
'
' Set_Print Macro
' set printing area and print
[Code]......
View 3 Replies
View Related
Dec 3, 2013
I am using a lot of linked reports that have to be rewritten each month. For example smaller formulas look like this:
=('S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$228*2)+'S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$262+'S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$292
What I want to do is extract the file path from the above formula and make it a composite of several cell references.
So what I need is to have a cell where they can change the month and another where we can change the year. So I set up several named cells that look like this:
_MONTH =11 November
_YEAR =2013
_JOBCARD ='S:PUBLICProductionJob CardsMOLDING
_PATH =_JOBCARD & _YEAR &"" &_MONTH
I tried several versions, I am hoping for something like this:
=('_PATH &"[440A SIDE SPOILER JOB CARD.xls]"Production Parts'!B$228*2)+'_PATH &"[440A SIDE SPOILER JOB CARD.xls]"Production Parts'!B$262+'_PATH &"[440A SIDE SPOILER JOB CARD.xls]"Production Parts'!B$292
View 4 Replies
View Related
Oct 22, 2009
I have a number of statements within the Sheet Event Code (Excel 2007). Three times lately I have added a column and had to go back into the code and find all of the references that needed changing to reflect the new column.
I have been working on this for a couple of days and even tried EE, but to no success.
I have read that Defined Names / Constants should be used as often as possible, but even trying that, the VBA code errors out or "hangs up". Even within Bill Jalen's book (VBA and Macros 2007), there is nothing that addresses this, especially using Intersect.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
On Error GoTo mEnd
Set rng = Sheets("Log").[F14:F10000]
If Not Intersect(rng, Target) Is Nothing Then
If Target = "" Then
With Sheets("Log")
View 9 Replies
View Related
Feb 10, 2014
I set up formulas to count text characters in a range of cells. I'm tracking attendance and payments for a small yoga studio.
All I need to do is count "Y"s for prepaid attendance and "DI"s for drop-ins. I have the formulas working but they are absolute so inserting a row will break my sheet.
=COUNTIF(E14:Z14,"*Y*")
=COUNTIF(E11:Z11,"*DI*")
View 1 Replies
View Related
Nov 25, 2007
It is suppose to be that if the employee is "FT" and has worked >=4 years the return is 15. But if the employee is FT and has worked 2 years but less than 4 years then it is suppose to return 10 (these are days off) Or if the employee is FT and has worked 1 year, but less than 2 then it should return 5 days off. And all the others in the column get no days off.
I have tried to do it with structured references and with cell references I get a column of zeros!
View 9 Replies
View Related
Mar 2, 2009
I am using the dsum formula to sum some values...the formula in B2 is:
=DSUM(BaseSistemasFebrero,"vlfinf",OFFSET('Planes Entidades'!B$1,0,0,COUNTA('Planes Entidades'!B$1:B$49),1))
The Planes Entidades sheet the data is layed out like this: ....
View 9 Replies
View Related
Dec 6, 2013
I have a file from a database that includes birthdays and anniversaries. These are mainly in US format as far as I can tell, but I am trying to get these all in UK date format but when I try and change any of the formatting half of them don't seem to change.
I have attached the file : Dates.xls
View 2 Replies
View Related
Feb 22, 2006
I want to be able to add a column with mixed numerals. For example: The sum
of A2:A34 with the values in the cells being 1B or 1F or so.
View 11 Replies
View Related
Jan 25, 2013
For example if you want C23:C99 and column H23:H99 as well
Meaning a For each loop would display contents of
C23
H23
C24
H24 etc.
View 8 Replies
View Related
Sep 23, 2008
I have a mixed string (i.e. containig character and numbers.... but the format is same PAR1 or PAMR1 or PR10 it can be 4 characters or five) i want a formula or a macro which splits it in two parts one containing characters and the other one containing nos.
View 9 Replies
View Related
Jun 17, 2014
A column holds numbers and numbers with suffixes. The conventional sort function pushes the text values to the bottom. I need them sorted along with the numbers.
Microsoft's KB says to add a column formatted as text and then to RETYPE ALL OF THE VALUES! Splendid. But my file has hundreds of records. Re-typing all of them would be a major pain. On the other hand, if I had started typing while trying to find a way to sort the darn thing I might be done by now.
Here's what I know so far: If we create a column, format it as text and then populate with 1, 2, 3, 4, 1a, 2a, 3a, 4a, etc. it will sort exactly correct (1, 1a, 2, 2a...) after selecting the "sort numbers and numbers stored as text separately" when the Sort Warning appears. If you try to create the sample I typed here, you need to be careful that the area is formatted as text BEFORE entering the text. You apparently cannot change the format of an existing column of data to Text or if you do it does not have the desired effect when sorting. It might be useful to know if this can be done too. MS suggests not.
View 11 Replies
View Related
Apr 14, 2007
This is what I need to be doing:
Write cell K5 as a relative reference, absolute reference, mixed reference with the row varying, and mixed reference with the column varying.
How do I do that?
View 8 Replies
View Related
May 8, 2009
Counting with text and symbols mixed in. This is what I'm wanting to do:
For example:
View 5 Replies
View Related
Dec 4, 2012
How can I make Excel create a column with only email addresses from a document that has all contact data of each customer in it? The column that contains the e-mail addresses also contains phone numbers at present...and many blank spaces in between addresses.
View 9 Replies
View Related
Mar 20, 2009
i've got supplier list on the excel file and need to insert two blank rows under each supplier name. the two blank rows need to be inserted no matter whether the supplier may be occupying one row or 100. for example
from this
a ltd
a ltd
b ltd
b ltd
b ltd
x ltd
m ltd.........
View 2 Replies
View Related
