I got a list of data in many row. I need to erase some rows, With a macro, how do i select the actual row position.
I want to place a macro button on each row created. By pressing it, i want to activate a macro to erase the entire selected row.
All i need is to know how to select the active row of the selected cell activated.
Is it possible to position a predetermined cell (e.g. A42) in the top left corner of the screen. (Not every screen users use has the same size)
View 3 Replies View RelatedUsing VBA, I need to Select A1:C14.
The problem is that A1:C14 contains blank cells, and there is also an adjacent column D that I do not want to copy.
So, UsedRegion and CurrentRegion aren't doing it for me. (It selects Column D too.)
Obviously, this is an example...the real data set is an export and varies in size.
I need to find a number (1, 2 or 3) and I am looking in two different cells for it. Now, Cell 1 might have the information; Cell 2 might have the information or BOTH Cells A AND B might have the information, or either cell (or both) might have different information. I can't do an IF/OR because I don't know which cell will have it, nor can I do an IF/AND for the same reason. I can't assume that they will both have the information -- so I can't just use a solution that includes a "divided by."
for example:
A
1 2
2 0
or maybe
A
1 3
2 3
or
A
1 4
2 1
If A1 or A2 is > 0, but <4, then that number
how to get a date to return what the actual day? ie 13/01/09 is tuesday?
View 3 Replies View RelatedI constantly use the following to determine the last row in a worksheet for loop control purposes.
[Code] ......
In my current problem i have a worksheet that has been formatted such that the code above returns a value of ,say 150 where as the actual data stops at say row 34.
If I "watch" the value in Col A I see A34 = "1513(a)" which is valid data and A35 = ""
I can easily cycle through the worksheet and calculate the last used row myself but I am always looking for ways to let "VBA do the heavy lifting" when I can
I have a list of 5,000 urls and I'd like to see what the actual url that is returned for each of them. For example, if I run the url www.example.com/runningshoes, that url may redirect to www.example.com/shoes. I'd like to see what each url returns. Is there a formula/macro that I can use to see this? I'm not very experienced in using macros, but I'm willing to give it a shot if it'll work.
View 2 Replies View Relatedonce bTest is declared as a Boolean, it becomes equal to FALSE (I checked this after much head scratching). How does one assign a value to a boolean variable only after you make your test.
Sub foo()
Dim bTest As Boolean
If Range("a20").Value = False Then bTest = False '
I have a spreadsheet with a large amount of conditional formatting and formulas within it. In order to make the file smaller and more manageable for management, I need to copy the sheet into a new book and then paste special values it.
However, upon doing this, the conditional formatting remains active and so my formats all change.
How can I copy the formats, but not the conditions?
I am looking to get a Live Clock changing every minute..
Lets say I put the Total Time in the Cell A2 = 15 minutes and I have a Command Button next to it "Start"..
On the Click Event I want the Actual Time time-stamped at that moment in the Cell A3 and in Cell A4 the Time should keep on Ticking...
Can someone attach a file in Windows 2003 i.e .xls format and also explain the code if possible.
a colleague recently used my pc and "messed it up". when i enter a value in any cells, it is divided by 100, eg, when i enter 1, it shows as 0.01.
View 2 Replies View RelatedOn my worksheet, cell A1 and A2 both contain the value:
0.1655555
I've changed the number formatting to three decimal places so it appears on the sheet as 0.166.
In cell A3 I have a forumla =A1+A2 which currently comes back with the value 0.331 (0.1655555+0.1655555=0.331).
Instead of adding together 0.1655555 I want it to add the displayed value of 0.166 so it should then come back with the value 0.332.
See xls file. I have this chart and the data labels are percentages instead of the actual value. How did these get that way? Under Chart Options there is a autotext box that is there but if I uncheck it, the data labels go back to the actual dollar value and that autotext box goes away.
I am totally baffeled by this despite serach the help, the internet and this forum. I am sure it is something easy I am missing.
Can some one provide some color on this?
I cut this from a larger book with many tabs because I am not able to send out the entire thing (proprietary).
I am trying to edit the data in the actual pivots rather than editing in the data source. i know there is vba code to enable pivot table editing.
How to enable this.
Place the actual date in a cell. Here is my code
View 2 Replies View RelatedI discovered the following Macro (on another site). What I'm attempting to do is make data entry simple by entering a date such as 012209 and have a macro convert the entry to 01/22/09 as a legitimate date field. On entering 012209, the macro displays a value of 09/01/2021 with an actual cell value of 1/9/2021. I expected a value of 01/22/09.
Entering 010109 displays a value of 01/01/2009 with an actual cell value of 1/1/2009. I expected a value of 01/01/09 (I can live with this, just didn't expect a 4 digit year).
i am creating an add in that will allow me to display all pivot tables in a workbook, their name, sheet location and source data in al listbox (called LBPivot) on a form (called FrmPivot)
My problem is that i am not sure how to make sure it points the focus to the activeworkbook and not the actual add in workbook?
i have this file with macro. But it doesnt works how i want. I need all data exctly in forms as are. So it can not will be changed. I hope it will be works, if i define my macro other.
I find actual day in column C where are dates. But now it find only if is there value like now(). In this file it found cell D24, but i need, that it will find cell C24. I need search only actual day in column C.
I have a column A with date formatted as dd/mm/yyyy. I then have a column B which is =A, and I reformat the date formats to a custom format DDDD which gives me the day of the week that the date relates to.
I then try and write an if statement
e.g. =If(b1="Friday",1,0)
B1 always references the date which is stored as 40118, when I need it to reference the actual formatted value that I see in the cell e.g. Friday?
how would one change the actual code words - instead of using if statements i would like to have when statements that do exactly the same thing, i just want to write "when blah then blah"
View 9 Replies View RelatedI have this formula in E19: ...
View 9 Replies View RelatedI have created the following code by recording a macro. The ActiveCells (“A1.A5203”) were based upon the actual number of records at that time. I wish for this macro to be used in a template and the actual number of records will vary from time to time. What do I need to add to this code to allow the AutoFill function to operate and fill only the actual number of records that exist.
ActiveCell.FormulaR1C1 = "=LEN(RC[-2])"
Selection.AutoFill Destination:=ActiveCell.Range("A1:A5203")
ActiveCell.Range("A1:A5203").Select
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]=8,""0""&RC[-3],RC[-3])"
Selection.AutoFill Destination:=ActiveCell.Range("A1:A5203")
ActiveCell.Range("A1:A5203").Select
I've attached a simplified worksheet which tracks the calls of a team of salespeople along with a pivot table showing their results for Jan-Mar. I've also included a summary of their planned calls for the period. Ideally what I'd like to do is have the pivot table show actual calls, planned calls and variance for each salesperson and each month as seen on A37-J42.
View 6 Replies View RelatedI am working on a spreadsheet for my farm. I have two sets of data I am working with. A very basic example is below:
What I am trying to do is find the actual median stud fee (i.e. if you had another column with $5000 listed 22 times, $6500 listed 34 times, etc. then you could find the median from there).
Seems like an easy thing, but I'm not sure if an array formula is what I need to use or something simpler. I don't have a lot of data right now (only a page) but will have much more soon.
I am trying to edit the data in the actual pivots rather than editing in the data source. i know there is vba code to enable pivot table editiing. how to enable this
View 14 Replies View RelatedI need to count the actual number of days between 2 dates but excluding Sunday...
For example,
Start Date-----End Date-------# of Days
01/Jan/14-----23/Jan/14-------20 days (excluding Sunday)
In some cases, Holiday also to be removed i.e. 01-Jan-2014 is holiday, then the # of days should be 19 days.
If I use Networkdays formula, then system results excluding both Saturday and Sunday. But I want to remove only Sunday.
I use Excel 2000 (9.0.2720)
In range A1:D10, I have entered the figures in actuals (e.g. 34532642.45, 78945624.89 etc.). How can I convert those figures in Lakhs / Thousands / Crores? Before converting, Can Excel 2000 prompt me for conversion in Lacs / Thousands / Crores?
I am trying to create three different conditional formats for my date tracker. I have attached an example of what I am trying to accomplish and tried to write out what the rule's are, just struggling on how to write the formulas.
Condition 1 - If the cell in column C's date is past due, change the cell red only if there isnt a date in the "Actual" column B
Condition 2 - If the cell in column C's date is coming up in the next 30s change the cell to yellow only if there isnt a date in the "Actual" column B.
Condition 3 - If there is a date in column A and Column B turn column C's cell green.
I am using this formula "=+IF(B3="",A3,B3)" to populate Column C.
Conditional Formatting.xlsx
So I am trying to make a macro that will update monthly forecast data to what the actual production/consumption data was. The production/consumption numbers come in separate workbooks (i.e. "Jan14", "Feb14") and need to be linked to the 'Book' main file. The path needs to the Jan14 needs to come into the 'Book' file, not just the values. I would like to be able to just have a button above each month that will just automatically load that month's data when it becomes available.
View 1 Replies View RelatedDown loading data I receive dates as a number that indicates YYMMDD (ex. 130603 is 03 JUN 13). Is there an easy way to convert these number strings to the actual date indicated other than typing it in?
View 5 Replies View Related