Macro Adjustment
Feb 27, 2010
A board member helped me with a macro, but my example had the wrong columns, and I did not know how to adjust the macro.....
I have 3 columns.
Column 'W' Needs to return a value derived from 'fourth and fifth character' of Column K.
Column 'X' needs to return a value for the Sixth and 7th Character of Column K.
Column 'K' is a 7 Alphanumeric part #. example AI3-HDSS.
(first three alphanumeric characters change, but are not relevant.
The 11 combos are all the combos in the spreadsheet.
Col. Y
Col Z
Col AA
Col AB ...
View 9 Replies
ADVERTISEMENT
Feb 6, 2007
code is pulling data for forecasts for the following 10 days, and the code is the following:
WHERE (((dbo_ACTUAL_HDD_DAILY. DATE)>=Now()-1 And (dbo_ACTUAL_HDD_DAILY.DATE)<Now()+9)
All he wants modified is to pull data for the entire current Month (Ex. if it is in the middle of July, he would want the data from July 1-July 31, or if February from Feb 1-Feb 28) It would be nice to do this without having to change the VBA every month.
View 4 Replies
View Related
Oct 1, 2008
I am trying to find a way to have a cell look into a group of other cells and display the first available things it comes to. Then have the next cell look in that same group and display the next item.
cells A1:A5 have 3 pieces of information in them scattered among that column (A1, A3, A5 might have the info in it one day, then A2, A3, A4 the next day)
I want B1:b4 to find the info in the A1:a5 and display it in order as it appears in the A column.
View 9 Replies
View Related
Nov 3, 2008
I am building a sheet of sales targets for 2009. With each month allocated a certain percentage of the annual target.
I wish to be able to take into account a change of target at some point in the year.
If i were to change the annual target in June, i need the spreadsheet to only change the monthly targets from June onwards, January - May are finished.
In the example attached there is a change in annual target in June. How do i calculate what the remaining month's targets need to be in order to meet the annual target while taking into account what has already been achieved and the shape of the budget as indicated by the percentages??
View 5 Replies
View Related
Feb 15, 2012
I'm trying to determine how to indicate which month an adjustment will post to an invoice.
Column A= billing cycle date
Column B= Market
Column C= Adjustment Approved Date
Column D= Adjustment Amount
Column E = Which invoice will credit post to:
So I'm trying to build a formula in Column E that will look at the cycle date in Column A compared to the Adjustment approved date in Column C and then kick out which invoice the adjustment will appear on. The values in Column E were placed mannually to show what I'm trying to accomplish. if the adjustment approved date is = to a cycle date it will show up on the same invoice. ie if approved on the 1st and the cycle date is the 1st the invoice will reflect the approved adjustment.
ABCDE1Cycle Day of MonthSales MarketAdjustment Approved DateAdjustment Amountposted invoice21Salt Lake12/15/2011-$1,300.00Jan '1232Denver12/22/2011-$3,802.01Jan '12411Atlanta1/12/2012-$5,292.00Jan '1255Dallas1/23/2012-$6,000.00Feb '12628New York2/1/2012-$5,000.00Feb '1272Denver12/5/2011-$500.00Jan '1283Seattle2/4/2012-$440.74Mar '12912San Diego1/4/2012-$500.00Jan '12101Phoenix1/17/2012-$257.87Feb '12112Denver1/18/2012-$1,220.92Feb '12123Seattle2/5/2012-$911.03Mar '12134Spokane1/30/2012-$20,391.86Feb '12145Dallas12/6/2011-$45.63Jan '12151Phoenix12/7/2011-$7,176.14Jan '12
View 2 Replies
View Related
Aug 22, 2007
The code below puts a green border around the cell that is beneath 10 in my chosen range, however I wish to add the border to the row of information instead of just the cell. My columns of data are from columns E to M, but the criteria for whether or not the data gets a green border is in column D....so lets say D15 is less than 10, I would want a border to go around E15:M15.
Sub Test()
For Each c In Range("D2:D350")
If c < 10 Then
c.select
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 4
End With
View 9 Replies
View Related
Oct 28, 2008
I need to paste a picture from the Clipboard to my Worksheet. I select the origin and paste it with the macro.
I need to adjust that picture to fit in a defined space from left corner of Range($J$10) to the right corner of
Range($BJ$35)
Actually, i'm using this procedure
ActiveSheet.Unprotect
RANGE("graphique_PL").Select
ActiveSheet.Paste
Selection.ShapeRange.LockAspectRatio = msoFalse
Selection.ShapeRange.Height = 358.25
Selection.ShapeRange.Width = 725.
The problem with it is, the Height and Width is arbitrary to the size of the cells at the moment. I would like to had a procedure to calculate does value. They represent the distance between the defined cells location for the image. Actually, if cells width or height change, the picture is misplaced.
View 9 Replies
View Related
Feb 1, 2009
Im having alot of difficulty preventing the result FALSE when one or more of my >20 count within an index table doesnt have a result to display.
Is there anyone able to understand the following? That can perhaps provide a solution that returns no FALSE word??
=IF(ISERROR((VLOOKUP($A22,'C Number'!$A:$N,B$1,0)))=FALSE,VLOOKUP($A22,'C Number'!$A:$N,B$1,0))
Ive tried ISNA but I always get an error appear when i try to use it, perhaps you could edit the command above so that ISNA works whenever FALSE is the result?
View 9 Replies
View Related
Jun 29, 2009
I have Office 2007 and i use this code on my word.docm to insert selected photos. the problem I'm having is that it insert photo at top of page. can additional code be added so that it will insert photo in same table as command button. and in front of button, so that it will hide button
Private Sub CommandButton1_Click()
Dim sFileName As String
Dim ilImage As InlineShape
With Dialogs(wdDialogInsertPicture)
.Display
If .Name "" Then
sFileName = .Name
Set ilImage = ThisDocument.InlineShapes.AddPicture(sFileName, , True)
With ilImage
'set any additional properties such as left, top, etc., here
End with
Else
Exit Sub
End If
End With
End Sub
View 9 Replies
View Related
May 19, 2014
when "Update"(code is under "Update"button) button is pressed to copy the data from userform to the database sheet exactly into columns where both column heading match, for example if userform has heading "Qty Received " all data from that column should be in the database column with the same header "Qty Received"
I attached my file when you will open the file you will find screenshot how it should look.
View 14 Replies
View Related
Jul 18, 2007
Have this formula which works fine for finding the largest sequence in a list. (c/o Domenic from [url]
=MAX(FREQUENCY(IF('Overs-Unders'!B3:B1827"",IF(ISNUMBER(MATCH('Overs-Unders'!B3:B1827,{0,"n/a"},0)),ROW('Overs-Unders'!B3:B1827))),IF(('Overs-Unders'!B3:B1827="")+ISNA(MATCH('Overs-Unders'!B3:B1827,{0,"n/a"},0)),ROW('Overs-Unders'!B3:B1827))))
Now i need to:
(a) from the cells B5:CC5 that this formula runs through find the highest figure and return the name in Row 1 of that column.
(b) adjust above formula to get something now that ignores any run that contains 7 or more consecutive "n/a"s
(c) get a formula that counts the latest run. eg. from the bottom up (at the moment data only goes down to row 200)
View 9 Replies
View Related
Aug 13, 2014
Is it possible to modify the attached code so that it will copy bold text and border as shown in attachment sample1 and paste in sheet Shop. Currently the code just copy's and pastes without bold text and borders.
Sample1.PNG
View 4 Replies
View Related
Nov 7, 2009
I've adjusted a jonmo code to add an item in col B which is not in col A to the bottom of col A. - fab code, thanks jonmo.
But.. i want to:
insert rows beneath those in column A to accommodate the added items and shade those cells in list A once they been added ( so the users now they've been moved )
I've posted the code below ( including my attempts at colour change where it shade the right cell but in the wrong column ) ...
View 9 Replies
View Related
Jun 15, 2014
Assume I have a cell M24 with a formula like
=M10 + $H24 - $I24*0.35
As you can see B10 is a fix reference (due to omitted $) which should NOT be auto-adjusted but be kept.
Now I want to copy the formular to lots of cells below cell M24. therefore I mark cell M24 and click copy in context menu.
Then I drag/expand the blinking cell border to lets say the 20 cells below. As I result I expect e.g. in cell M25 a formula like
=M10 + $H25 - $I25*0.35
Unfortunately I got
=M11 + $H25 - $I25*0.35
So the fix reference is adjusted as well.
How can I tell Excel 2007 to NOT auto-adjust fix references in formulas?
View 2 Replies
View Related
Oct 16, 2009
I am trying to make a save&close workbook macro.
I found several examples on google, but unfortunatly it conflicts with another macro I use for forceing users to enable macros (hide all sheets except one if macros are disabled).
The attached file is an example contaning the save&close code and the show/hide sheets depending on macros enabled.
If the file is opened with macros disabled then only one sheet will be visible.
If the file is opened with macros enabled other sheets are visible.
The problem if that this code uses a custom save, witch makes the save&close not save... (in module1 and in ThisWorkbook)
The pourpose of the save&close is to make sure some users don't forget the excel open and thus block access to it. So if a certain idele time passes excel has to save and close without any confirmation messages.
View 10 Replies
View Related
Jul 10, 2012
I'm using a CALL Macro to split up a HUGE macro into different pieces:
Code:
Sub RSLDASHBOARDV2()
'Macro recorded 12/14/2010 by Ryan R. Koleno, Pharm.D.
'Last Updated 7/10/12 by Ryan R. Koleno, Pharm.D.
'Do Not Modify Code Unless Given Proper Privileges to do so.
Dim APPSPD As Worksheet
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
[code]...
The first few macros dealing with page setup and what not work fine but when it hits the Pivot table code for the STATSPIVOT macro it errors out stating: "Run-time error '1004': Unable to get the PivotItems property of PivotField class' at this point in the code:
Code:
objField2.PivotItems( _
"TRC").Position = 1
objField.PivotItems( _
"MEDCO MAIL OR AOB").Position = 2
When this macro is not split up it worked fine as written. Am I overlooking something in the Call Macro's code or is there a variable I'm not aware of. I have included the Pivot Table code that errors out as well.
Code:
Sub STATSPIVOT()
'STATS PAGE BASED ON STATS DATA TAB
Sheets("STATS DATA").Select
Dim objTable As PivotTable, objField As PivotField
ActiveWorkbook.Sheets("STATS DATA").Select
Range("A1").Select
[code]...
View 4 Replies
View Related
Jul 21, 2007
I am making a spreadsheet that sorts and pastes, but I need to know if I can add a code to the Sort and Paste Macro that will open the second spread sheet needed without just already having it open and using the
Windows("estimate sheet one.xls").Activate
View 2 Replies
View Related
Jan 15, 2014
I have a macro where I manually select a cell then the macro kicks in to copy and paste the contents into the Find function. From there it goes to another work sheet, clicks on a cell in column one and searches for matching cell contents. Then If false it manually goes back to the original worksheet/cell and then I input a N in the left adjacent cell. If true I enter a Y. Right now I can only run this for the specific cell the macro was recorded for. I would like to expand this.
What I would like to do create a loop in the macro that waits for my cell choice input then continues with the with the Find function. If false the macro should just loop back to the original worksheet/cell and wait for input, ie the next cell selected. However, if true a worksheet/cell is selected, a Y is input and it loops back to the original cell and waits for input.
View 9 Replies
View Related
Aug 13, 2014
This is the code I use to call a macro when the macro Im calling is in the same workbook.
[Code].....
However, I would like to call this same macro when using another workbook. I copied the macro "Clearformating" and pasted into a personal macro workbook module. However when I add this code to the sheet tab it will not run the macro.
I also tried this code.
[Code] .....
View 2 Replies
View Related
Jan 26, 2009
I have a macro that I found somewhere on the net to look within a folder and list all the files of a certain file extension.
The macro to do this is in the attached example and is called 'Get_File_Names_Within_Dir_ext'.
I have created a basic userform outline, 'UF1' for the user to define:
Select File Extension
Select Folder to Search
Destination Sheet
I just don't have any idea how to sync the two.
If you type 'exe' into 'TB1_File_Extension' of 'UF1' the macro should search for '*.exe' files within the specified folder.
The search folder 'RefEdit1' box should open a windows explorer box (or some such) so that the user can select the directory in which to search for the previously specified file extension.
'TB2_Destination_Sheet' is a text box for the user to type the sheet within the workbook in which to list the files found within the specified directory.
'CB1_Find_Files' should activate the macro to find any files for the specified criteria.
There is also a Button 'Find File Types' in Sheet1 of the file which should activate the userform 'UF1'.
View 14 Replies
View Related
Feb 1, 2009
I have a worksheet in which I have a worksheet_change macro. This worksheet_change macro makes sure that a few cells will keep their colors, even if the user copies and pastes a new value to that cell. This worksheet_change macro runs each time there is a change on the worksheet. Now my problem is that on the same sheet I have an update list macro which updates around 20.000 rows and two columns (which is alltogether around 40.000 values) and it takes a while to run. So.. it takes a loooooooooot of time (too much) when these two macros both run.
My question is that can I somehow disable the worksheet_change macro while the update list macro runs. I mean something like when I start the update list macro to disable worksheet_change macro and when the update list macro finishes, then reenable worksheet_change macro?
View 5 Replies
View Related
Sep 17, 2009
I need a macro to get the values from cells D29 and H24 in the Resource Calculator sheet and populate it into cells N8 and O8 in the Input form.
Users will then be able to change the information in the calculator and click the macro again to populate N9 and O9 and so on.
Is there a way to do this?
I've attached the file for you to see.
View 13 Replies
View Related
Oct 3, 2012
I have a macro which refreshes a query when the spreadsheet is opened. This works fine when online.
However, if the user is not online, the query is unable to refresh and the macro just hangs.
Is there a code which will enable me to say " if unable to refresh then move on to the next line"?
here's the code below.
Private Sub Workbook_Open()
Sheets("Houselist").Activate
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets("Front").Select
Range("A1").Select
End Sub
View 2 Replies
View Related
Mar 4, 2014
I am looking for a macro that i can store in my personal.xlsb. what i need is pretty much is something like this
private sub workbook_open
if workbook.name "inventorysummary.csv"
then application.run "personal.xlsb!capacity"
end sub
I only need it to run just for this file and i cannot place it in the file due to it gets replaced every day. Which if it didn't get replaced. I know how to do auto opens when the file stays the same I am just unsure for this.
View 1 Replies
View Related
Nov 6, 2011
I use Excel 2003 at work. I'm looking for some code that will Open a folder and then lets the user to select a file then continues to run the macro.
The file name they select will look similar to this K2271011.504 or K3011111.201
View 3 Replies
View Related
Mar 16, 2007
1st - Need a macro to change a range of cells colours based on a single cell having a value greater than 0.001. ie. cells A1 - G1 need to change to grey based on cell F1 having a value greater than 0.001 entered in it?
2nd - Also a macro for deleting the text contents of cell C1 based on cell F1 having a value greater than 0.001. Therefor if cell F1 has a number greater than 0.001 it changes the colour of celss A1 - G1 and also deletes the text in cell C1?
View 2 Replies
View Related
Jul 16, 2007
I have written two VBA programs around the same time. Both run on open and pull external data and create graphs. My problem is that I want the end user to be able to run the report multiple times by choosing the name of the macro from the Excel macro menu (i.e. Tools>Macro>Macros) but only one of the workbook macros shows up on the menu. why the other macro is not visible on this menu???
View 2 Replies
View Related
Dec 15, 2008
I've recorded a macro that copies an entire tab into a new spreadsheet then goes on the copy and paste information from one tab to another.
When I run the macro from the Tools>Macros menu it works perfectly.
But when I copy the code and add it to that of a button it fails and posts the following error: Run-time error '1001': Select method of Range class failed.
The first attachement shows the code for the macro as it is alone, and the second shows how I simply copied and pasted it into the 'view code' window of the button.
Needless to say I'm a beginner at macros and only every record them, I can usually make stuff work that way but this has me stumped!
View 12 Replies
View Related
Jan 11, 2010
Put in a statement within a Macro that populates cells with the values that I want it to but instead of populating all at the same time, is it possible for the values to be delayed.
I have designed a mock spreadsheet (attached) it has two columns 'Before' and 'After'
After = Before values (in this mock)
When you press the button, the values are populated straight into the 'After column' can we add the delay between the values? So that the values dont come up straight away.
View 3 Replies
View Related
Jan 27, 2010
Macro to export and import a module and assigning different macro automatically ...
View 9 Replies
View Related
