On column A, I simply do an A-Z sort that gives the expected result. So I recorded it as a macro. In column G is a code that corresponds with column A. When the marcro is executed Column A is sorted but the corresponding value in G stays where it is. Clicking the A-Z button works. But when that action is recorded as a macro I get the above quirck. Since VBA is not my beef, I don't really know what wrong.
View 4 RepliesMy sheet called 'Report' finds a type using a vlookup. I then have a sheet called labor, where I want to find put the total amount for labor using this formula: '=SUMIF(Report!$K$2:$K$65000;LABOUR;Report!$G$2:$G$65000)', but it only returns a '-'. I want the formula to return total amount from column G, if column K is type 'LABOUR'. Is my formula incorrect?
View 2 Replies View RelatedI have csv file with tab delimited fields (see attached blacklist.csv). 3rd and 4th fields are in format dd/mm/yyyy hh24:mi:ss, some fields can be zero. I need to split data to columns and display datetime fields in format dd.mm.yyyy hh:mm:ss.
OK... open csv in Excel, select column A, press button "Split in columns", select tab as delimiter, choose date format (DMY) for 3rd and 4th field and press OK. Test is splitted into columns, now select columns C and D and select desired format "dd.mm.yyyy hh:mm:ss". Then I select columns A:E and set column widths to fit data in columns. This all is OK and without problems (see attached file Handmade.xlsx).
But now... Now I record all previous step into macro. There is macro text: [Code] ....
Now I copy macro text into clipboard and open blacklist.csv again. Insert button to list and bind it to new macro. Paste text of macro from clipboard. (See attached file blacklist_beforeClick.xlsm). Now click button Btn and voila... some dates remain in previous format, some dates seems to be in desired format, but day and month are swapped. Why are there differences when comparing data made by hand and data made by macro??? compare.jpg This is the same behavior in more computers, some use Czech version of Excel 2010, some use English version.
I need to look at 2 different cells and have a 3rd cell provide a number based on the first two cells.
Column A: either 1 or 2 (2 results in +5)
Column B: either yes or no (yes results in a +5)
Column C: function would result in either 0, 5, or 10.
Is there a way to provide filter with a list of criteria but when it doesnt match all of the criteria it still uses the filter on the criteria that it does match?
E.g i have this code
ActiveSheet.Range("$A$7:$N$31997").AutoFilter Field:=1, Criteria1:=Array( _
"A", "B", "D", "E", "H", "I", "R"), Operator:=xlFilterValues
However sometimes for example B will be missing, or H or B H I will be missing etc... is there a way to provide all of the criteria and it will not error if the criteria is not all there?
What i have so far is:
-3 Sheets (Sheet1 - Search, Sheet2 - Data, Sheet3 - Result)
-Sheet1 has a userform ready to link up - this opens up when workbook and worksheet is activated.
-Sheet2 has some example data to search - the search is for column 1 (see attachment)
-Sheet3 has a template ready for the data to be pasted to.
I would like the search result is column 1 to copy the data from columns 1 to 7 to sheet3.
I want the template/layout to remain after the pasting of results. (everything protected from editing, if possible).
Once finished (ie go back to sheet1, i want the data from sheet3 to be cleared.)
I've been asked to assist with modifications of an excel spreadsheet. One of the features is a column that has a formula of what the total order should be. For example, 1 base order of 25.00, plus 1 bonus order of 18.00, plus 2 bonus orders of 15.00 would be $73.00 due. Then data entry persons enter the actual amount collected. Is there a way to highlight either the cell or the result if the amount collected does not equal the amount due? I know I can accomplish this with another column, but they want to keep the columns to a minimum.
This is for a non-profit agency and the orders are now at 1000 that must be entered within a day or two period, so they want to keep the fields to a minimum so as to make the entry easier for those doing the input. And they would like the data entry person to see an immediate flag if what he or she enters as collected does not equal what is due.
I want to clean up this recorded macro but not sure how, it highlights duplicate values in a column
[Code].....
shortening the below recorded macro?
Code:
Sub Macro1()
'
' Macro1 Macro
[Code].....
I know there are better ways of doing this but I am working with an existing form and I need it to look the same.
Column O on Sheet1 has a link that use to open a document, now all the information is on Sheet2. On Sheet2 I have a macro that hides every Project except for the Project Number the user selects in B4 of Sheet2.
Can I create a link that will:
1) Select Sheet2
2) Enter the Project Number from Sheet1, current Row, column D to Sheet2 cell B4
I know a link can select a worksheet so I have #1 covered but I am stuck from there.
I have recorded the following macro and made few changes to it with my little VBA knowledge. But the code still looks very messy.
Code:
Application.ScreenUpdating = False
Sheets("Sheet2").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;E:MacrosBSE Indices.iqy", Destination:=Range("A1"))
.Name = "BSE Indices"
.FieldNames = True
[Code] ..........
I have recorded certain macros. But after executing macro 'Undo' do not work. How can I do it ?
View 3 Replies View RelatedI have been using the "record macro feature". What I want the macro to do is as follows.
1. cut all data from column C and paste it into G.
2. Use the textTocolumns feature to split a comma delimited string into 2 seperate columns.
3. user the textToColumns feature to remove any leading spaces from column G.
4. cut columns d-H and shift them left startign in column C.
I can do all of this find bu using the GUI while recording the macro but when I check the vb script it's completely missing the paste commands.
When I try and run the macro it, quite rightly, returns "run-time error '1004':No data was selected to parse.
Below is the resulting recorded macro.
How do I amend this macro do include pasting the cut data?
I recorded a macro (which worked), then tried to get more out of it by adding code to add a further function (which didn't work). The code is as follows:-
View 3 Replies View RelatedI have a workbook where I need to sort data with range (K2:L24), then move down 208 rows and sort range (K210:L232) and repeat this upto 1000 times. I have shortened a recorded macro as an example and would like to know how to loop or repeat.
Sub Macro2()
'
' Macro2 Macro
' Macro recorded 6/04/2011 by Greg
[Code].....
Below is a recorded macro i am trying to apply on a pivot table.
Code:
Range("F4:H4").Select
Range("H4").Activate
Selection.AutoFilter
ActiveSheet.Range("$G$4:$G$100000").AutoFilter Field:=1, Criteria1:="21", Operator:=xlTop10Items
The problem i am facing is that when i am trying to run it is not working.
I've recorded a macro in which I unhide certain columns, copy and paste some information then hide those columns again. The problem is that when the macro is finished, it incorrectly hides columns K to AN. I did not record that and it's not in the code so I'm lost as to why it's happening.
The macro is as follows :
Code:
Sub COPY()
'
' COPY Macro
'
'
Application.ScreenUpdating = False
Columns("K:BT").Select
Selection.EntireColumn.Hidden = False
Range("M5:N24").Select
Selection.COPY
[Code]...
Why it's hiding everything from K to AN. I've tried recording the macro several times but it's just not working, no matter the order in which I hide columns when recording it.
I have a list of items in Sheet1 column A (starting from row 1).
I would like the corresponding column B to have certain values depending on the value in column A (same row), as follows:
If the value in column A has "SYS????CZ", column B should be "HPC"
If the value in column A has "SYSNIS", column B should be "NIS"
If the value in column A has "SYSJBE", column B should be "JBE"
If the value in column A has "ICG????", column B should be "HPC"
If the value in column A has "IL????", column B should be "RUP"
If the value in column A has "SYSHPC08", column B should be "HPC"
What I tried was to record a macro while typing the formula in B1: ...
Im setting up a spreadsheet that does engineering calculations. Im using macros to run sizes from a standard schedule. It basically takes the values from one sheet (schedule) to another (calculation), then the result from the calculation sheet (Value only, not the link) is pasted back into the schedule. The macro seems very bulky and im sure that it can be made more efficient with a loop. here is a sample of the code from the macro;
Sheets("Calc sheet").Select
Range("C6").Select
ActiveCell.FormulaR1C1 = "=Schedule!R[1]C"
Range("C7").Select
ActiveCell.FormulaR1C1 = "=Schedule!RC[1]"
Range("C8").Select
ActiveCell.FormulaR1C1 = "=Schedule!R[-1]C[2]"
Range("C9").Select
Sheets("Schedule").Select
Range("G7").Select..................
I manually create a pivot table (and record my actions) the pivot table references all of the information in my data range (70k+ lines). When I run the recorded macro the new pivot table limits the data range to the first 65536 lines (the old limit)....
View 6 Replies View RelatedIs it possible to record a macro or write VBA to apply conditional formatting to certain columns of cells where there are exceptions/conditions for the different types of conditional formatting? If possible, can those exceptions/conditions be based on a specific type of formula?
See my DATA EXAMPLE picture linked below. On that example, Columns K-Q need conditional formatting but manager wants row 38 to be blue based on the fact that it is using a =SUM() formula. This represents one "part" out of 75 that could be on a given sheet, each "part" has a different number of sub-parts that are used. So I can't just highlight columns K-Q because of the occasional SUM row that needs to be blue.
The conditional formatting that I am currently using in Columns K-Q only apply to K3:Q37 and not to row 38 at all. I currently have conditional formatting on columns K-N, P-Q where when '=ISNUMBER(xx)' returns TRUE is white and when it returns FALSE is light orange. Column O uses '=ISTEXT(xx)' for the same colors. Manager doesn't want to copy paste that formatting and wants it setup as a Macro/VBA.
DATA EXAMPLE - [URL]
RELEVANT INFORMATION
Columns K-Q need conditional formattingColumn K - Formula pasted in only on lines that require the Sales Price to show, will return a number value or error
(=VLOOKUP(B38,'SaleWS'!C:G,5,0).Column L - Formula pasted in every cell in column except L38 is '=IF(Hxx="G",IF(Exx"Description EX",VLOOKUP($Dxx&$Gxx,'PriceWS'!$D:$F,3,0),""),"")'.
L38 has a sum of all above valuesColumn M - Formula pasted in every cell in column except M38 is '=IF(ISNUMBER(Lxx),Ixx*Lxx,"")'.
M38 has a sum of all above valuesColumn N - Formula pasted in every cell in column except N38 is '=IF(Exx="Description EX",VLOOKUP(Dxx,'Material'!A:O,15,0),"")'.
N38 has a sum of all above valuesColumn O - No formulas here, just manual entryColumn P - Formula pasted in every cell in column except P38 is '=IF(H38="P",VLOOKUP($Dxx&$Oxx,'PressWS'!$A:$L,12,0),"")'.
P38 has a sum of all above valuesColumn Q - Formula pasted in every cell in column except Q38 is '=IF(H37="W",VLOOKUP($Dxx,'WeldWS'!$A:$F,6,0),IF(Hxx="T",VLOOKUP($Dxx,'WeldWS'!$A:$F,6,0),""))'.
Q38 has a sum of all above values.Rows where they are using SUM for all the rows above for that part need to be blue
I have following macro recorded to extract data from a mainframe system
open "C:DesktopReportRBDRBD.txt" for OUTPUT as #1
rc% = ps.sendstring("{clear}")
rc% = ps.sendstring("a24octdelbom*9w{enter}")
test1 = PS.getdata(450,3,27)
print #1,test1
The requirement here is run the above macro for the next 30 days automatically, for example
rc% = ps.sendstring("{clear}")
rc% = ps.sendstring("a25octdelbom*9w{enter}")
test1 = PS.getdata(450,3,27)
print #1,test1
and so on till 23 Nov. From the start date the macro should run for the next 30 days. Currently i am changing the dates manually in the macro
I have a button with a macro assigned. The macro needs to create a new worksheet at the end of the workbook, rename the new sheet to "New County", make the cell size match the rest of the workbook, and then paste in a template from another sheet called "Master".
I recorded a macro that does this, but it has a static reference to the new sheet instead of what ever the new sheet is. So the Macro works one time, but never again. I'm sure this is an easy fix, and perhaps there is a much easier way to do this, but my limited VBA knowledge is hampering me.
Code:
Sub AddNewCounty()
'
' AddNewCounty Macro
' Adds a new County sheet to the workbook.
'
'
[Code]...
I have recorded a macro to sort a range of cell based on two values that is dependent on time in another cell. I now want the macro to run automatically when refreshing the workbook with F9, so as the time changes so will the sorting. Everything works fine except the sorting doesn't refresh when F9 is refreshed.
My recorded macro is:
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim X As Long, FillCT As Long
For X = 1 To 4
If Cells(Rows.count, 1).End(xlUp).Offset(0, X - 1) <> "" Then FillCT = FillCT + 1
[Code] .....
I'm trying to use the "clear contents macro" for merge cell, but I keep receiving this "compile error : Expected End Sub" error.
FYI, I have named my merge cells to "myMergedCells"
[Code] .....
I am working on a project where I am virtually almost finished except for a minor change with the pie chart. I am analyzing some data and recorded a macro to do this and also the pie chart for visualization. However, I do not like the color of the default pie chart colors and would like to customize it. How could I change this within the macro I have recorded?
[Code] .....
I've recorded a Marco copying and pasting information from one cell to other cells and some of the copying functions paste to two cells. I just need this to loop through all information I'm copying from the the new cells.
Range("I1").Select
Selection.Copy
Range("D1").Select
ActiveSheet.Paste
Range("J1").Select
[Code]...
I have been given a macro recorded on a mac and it works fine with Mac Excel 2011. Unfortunately, I need to make it to work on a windows machine, but it comes up with an error message "macro unable to set width property of the window class. Run-time error 1004". When I press debug it takes me to the line .Width = 1456.
Below is the chunk of the code that becomes highlighted when pressing debug.
With ActiveWindow
.Width = 1456
.Height = 795
End With
I have a worksheet that has a sent date and expected delivery date I need create a macro that will alert me if today's date is within 5 days of expected delivery date.
View 14 Replies View RelatedI recorded a function and got the message box "Invalid Outside Procedure" when I tried to run the macro. I don't understand what it means or how to prevent it.
View 2 Replies View Related