I record this for retrieving some data from the web for a particular page
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.emiratesauction.ae/en/cars/OnlineAuction.aspx", Destination:= _
Range("$A$1"))
how I can make the URL be something in a particular cell on Sheet2 - cell B1?
Rather than having to give it a range, I'd like to have a generic range giving me room to add or subtract values in the column without changing this statement.
Code:
Worksheets("Sheet1").Shapes("Combobox2").ControlFormat.ListFillRange = _
"O2:O14"
I have a spreadsheet with 5 columns. A1 to A5
Entries are made into columns A2 to A5 but only ever one entry across all cells
If an entry is made into A2 to A5 the I need a "P" to be placed into A1
I am trying to create a macro which gives me an input box. I need to enter 2 numbers into the input box. It would be as follows:
Input Box Msg 1 - "What is your labor cost?" (NUM1)
Input Box Msg 2 - "What is your productivity rate?" (NUM2)
Then I need the macro to take this info and enter it into a formula which changes according to the row which you are on. (I need the macro to work based on which ever cell I currently have selected). The formula would be this if I currently had a cell on row 10 selected:
=(NUM1*(NUM2*$H10))/$H10
So, if I currently had cell L10 selected, and I ran the macro and put 100 in for "NUM1" and 10 for "NUM2" and my sheet had 20 in cell H10, then the macro would enter the number 1,000 into cell L10. If I had L500 selected, then the macro would enter the final number into cell L500 based on what value H500 contained.
I would like to know if it is possible to transfer values of cells this way?
I've attached the excel file for easier reference.
i need to define a cell as text. How can this be done? The reason for this is because if I type 001 in a cell it will automatically turn that into 1. Also this formatting would need to be copyable. So if I copy and paste that cell template and put in say 002 it would still read 002
View 8 Replies View RelatedI am just starting to get my feet a little damp with VBA and I am trying to make a macro that will act whenever any text is entered in any cell within a 3x3 square. Whenever any text is entered in any of those cells I would like it to enter a text ( "X" ) in a cell which is specified by a cell in the spreadsheet (this cell will output which cell the macro should write the text in in this format "A:1" or "C:3"). How can I make the program act only in reaction to the user entering thier text, and also, how can I get the macro to read A:1 and enter the text into that specific cell? Any help would be so awesome, the book I have is really difficult to navigate.
View 9 Replies View Relatedis there a simple way to do a vlookup (or any other more appropriate formula) using the value of another cell to define the named range relevant to that lookup.
eg I will have two different ranges for fitness performance levels, each of these will be named "Male" and "Female".
I would like to do a lookup based on a formula as follows: Try this formula. Column letter & Row number are for example only. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas). Many more Excel Formulas  Z100 =vlookup(D4,NAMED RANGE,2,false)Â
the Named range would be a value in the same row ie B4 - being Male or Female
so I am hoping for something like Try this formula. Column letter & Row number are for example only. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas). Many more Excel Formulas  Z100 =vlookup(D4,B4.Value,2,false)Â
otherwise I could just use an If and do something like: Try this formula. Column letter & Row number are for example only. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas).
Many more Excel Formulas  Z100 =if(B4="Male",vlookup(D4,Male,2,false),vlookup(D4,Female,2,false) )Â
I'm trying to create a drop down list that will define the name of the cell so I can reference it in formulas or codings to change the format of various cells defined by a specific name.
I'm not even sure if I can have multiple cells defined as the same name
I'll be inputting data into cells, and I want the color of the cell to change depending if the data is one of these criteria: 'In Service', 'In Transit', or 'Must Purchase'. I can't just make separate criteria columns to list all the data, because the data is already placed in a specific order. So I want whoever is viewing this to know which entry fits which criteria from the color of the cell.
The only thing I could think of is inputting the cell data value, and a drop down menu to define the cell name. I'll input a conditional formatting function to format based on the defined name criteria.
I have 3 variables right now...
Dim celadr As Range
Dim cLetter As String
Dim avgRow As Integer
and one line of code reads:
celadr = Range(ColumnLetter(LastScoreColumn) & avgRow)
When I mouseover columnletter it shows "H" and when i mouseover avgRow it shows "2"... this info is right but I think the problem may be trying to combine a string and an integer value as a range. How can I do this so that celadr is the range H2?
How can I define a range, only for the active cell?
View 6 Replies View RelatedI have the following function and cannot get the "cut/paste" section to work (After sOp = "Look at timestamp and determine which entry is newer".
Code:
Sub DeleteDuplicate(WSName As String)
On Error GoTo ErrorHandler
Dim sOp As String
[Code]....
The way I have try to define lastcell is wrong. But I don't know how to do it.
Code:
Sub createUniqueRecordID_2()
'
' fill using CTRL + ENTER
'
Dim lastcell
Sheets("projects list").Select
[a1].Select
[Code]...
I want to define the range from a given cell and all data below within the same column strDataStart is the named cell in the worksheet that want to start from. Below is what I tried and failed.
Function rngDataCol(strDataStart As String) As Range
Dim rngDataEnd As Range
rngDataEnd = Range("strDataStart").End(xlDown)
rngDataCol = Range(Range("strDataStart").Address, rngDataEnd.Address)
End Function
I'm having trouble assigning a range of a single cell using vba. I'm doing this to create charts. Here's the sub containing the problem
Private Sub AddNewSeries(Target As CTarget)
Dim i As Integer
Dim Match As Boolean
Dim rValid, rInvalid As Range
i = 2
Match = False
Do While Match = False
If Sheets("Graph Data").Cells(1, i) = ReportTargetBox.value Then
Match = True
MsgBox ("report name match")
Exit Do
Else: i = i + 1
End If
Loop.....................
This msg box :MsgBox ("Cell Value = " & Sheets("Graph Data").Cells(2, i).value) is working and returning the correct value.
I have a range staring with cell "A5" and going to Column "K8+" that I need to copy. My issue is that the number of rows to copy will change. I have the number stored a variable "a" in my coding. how do I code this to work?
View 5 Replies View RelatedI am trying to find a way to display text that is the product of a concatenate function in a defined cell, but not to have the function itself in that cell. Basically, I want to have the below function in cell A1. I want to add a command to it to take the result and display it in cell A2.
=CONCATENATE(C5,"_",'Attachment 3-A'!C9:E9,"_",IF(C5="","",IF(C6="MPL",IF(VLOOKUP(C5,MPL!B:F,5,FALSE)="Lease","Lease_Contributable",
IF(VLOOKUP(C5,MPL!B:F,5,FALSE)="Pre-Lease","Managed_Pre-Lease",
IF(VLOOKUP(C5,MPL!B:F,5,FALSE)="Non-Contributable","Managed_Non-Contributable","Error"))),
IF(VLOOKUP(C5,Sale!B:F,5,FALSE)="Assignable","Lease_Assignable","Managed_Non-Assignable"))))
The reason for this is that I need the cell to be selectable (it is generating a file name that needs to be selected and copied), but I don't want the code behind it to be seen. I can't find a way to make the cell selectable AND hide the formula from being seen when the cell is selected. This is because our people keep copying the formula rather than the resulting text.
I was hoping for something like a DISPLAYIN(target) function, but it doesn't seem to exist.
I would Like to define each row in a spreadsheet to the value of the first cell in each row and I have no idea how to go about this.
Code Customer
_0888 Williams
_0435 Smith
So basiclally I would like to name the first row "_0888" being 2:2. But without doing each row manually via Insert --> Names --> Define..
= IF(AND(MATCH($B8, [AXS.xls]Sheet1!$E$1:$E$400,0) - 2 = $C8,
INDEX([AXS.xls]Sheet1!$B$1:$B$400, MATCH(CX$2, INDEX([AXS.xls]Sheet1!$B:$B, $C8 + 16):INDEX([AXS.xls]Sheet1!$B:$B, $C9), 0) ) ),
INDEX([AXS.xls]Sheet1!$A$1:$E$400, MATCH(CX$2, INDEX([AXS.xls]Sheet1!$B:$B, $C8 + 16):INDEX([AXS.xls]Sheet1!$B:$B, $C9), 0), 3), " ")
i have this formula, and i think it will work if i find a way to correct the blue areas. they are trying to define the match array. i tried using the address and cell functions with no luck. i just want the array to return something like B2:B15 (from the AXS sheet) so the match function works. in the current sheet, C8 and C9 have values that refer to rows in the AXS sheet
I am trying to populate a cell based on which number meets the criteria I define. This is based on sales revenue, so if the revenue is less than $6.5M, I want to use a certain value. If the value is equal to $6.5M but less than $8M I want to use another value and finally if the revenue is greater than $8M i want to use another value. Here's my formula, but it returns $0.
=IF(B2<6500000,Bonuses!J45)*OR((B2>6500000)*AND(B2<8000000),Bonuses!F45)*OR(B2>=8000000,Bonuses!B45)
I want to define a cell range using a fixed column letter and then row numbers that are held as values in the spreadsheet somewhere. So for example I want to enter a formula = SUM(A4:A35). The problem is that the ranges I want to define are irregular and the row numbers I need have been calculated elsewhere in the spreadsheet. Say the number 4 is contained in cell B35 and the number 35 is contained in cell C35. So I want to say something like =SUM(A"plus number taken from cell B35":A"plus number taken from cell C35")
View 5 Replies View RelatedI have a built a catalogue of records for various of my companies assets. Each asset has its own sheet in a work book and I have used a vb macro to include an automatic Index sheet at the front of the book. This takes the names of the various tabs, in the order I have them in the workbook and creates a simple listing in column A as an Index List
What I want to do is to build a summary table to the right of the Index List, pulling in a few of the key data points about the assets into a single table.
To do that I need to be able to extract the text string from the Index List and use it to form part of a cell reference pointing to a specific cell in the relevant individual Asset sheets.
I'm wondering if it is at all possible to have a cell comment automatically update depending on what data is input in the cell (via user input, VLOOKUP, etc.)?
For example, if cell A1 contains the text "CHARLES" with a comment saying "Employee of the Month", and is then subsequently updated with the new text "JOHN" (again, via direct input, a VLOOKUP, data validation, etc.) is there a way to have the comment automatically update to say something else, such as "Team Lead" for example?
I've considered using VBA to accomplish my goal, but am unsure how to compose an effective code to do so. I've also considered perhaps creating a named table filled with all the different comment possibilities I would like to have used in this cell and then inputting a formula in either cell A1, or the comment contained therein, that would then call the corresponding text from that table based on the data in cell A1. Honestly, I'm not sure that what I'm trying to do is even possible;
I am working in both 2003 and 2007 Excel.
This is a simplified version of my worksheet:
I have two cells, A2 and B2. When I enter a numeric value into B2, I want it only allowed when certain text values are in A2. I.E.: Allow any value in B2, but only if A1 is either "SYDN or "ADEL".
Now using info found in an old post, I can achieve this with a combination of data validation and code:
=OR(A2="SYDN",A2="ADEL") for the validation and for the code:
[Code] ......
The only problem is I also need to reset B2 if A2 changes to invalid data as a result of some other input to that cell (A2). In other words if B2 is showing "1" already and A2 were to change from "SYDN" to say "LOND", then B2 needs to be cleared. If A2 changed to "ADEL" then it doesn't need to clear but it is ok if it does because it is fine to re-enter "1" into B2 again.
I have attempted to attach a sample worksheet : Xl0000003.xls‎
Macro Based on an input cell’s (e.g. B12) setting of “yes” or “no”, format several other cells (e.g. B20:G20, B23:F23, C26:J26) as follows: keep the existing yellow shading if input cell is “yes”, change to grey shading if input cell is “no”. I would like this macro to run automatically whenever the input cell value is changed.
View 2 Replies View RelatedI have to design an input user form (a button that opens an input box or dialog box), which asks user to "Enter number of "columns"".
User enters a number i.e. 2 or 3 etc
It then asks user for the headings of these coulmns and places first column heading in A1 and second column heading in B1, third in C1 and so on ...
I want to be able to see the average rating for each employee.. Is there a way to put in the rating from one survey and have it automatically add it another cell and then have it clear the first cell?
Here is an example of what I would like to do: Say we get 2 surveys with one rating of 5 and one rating of 10... If I input the 5 into cell Q4 and have it automatically put into cell R4 and then it gets deleted from Q4 but saved to R4... and then I can add the rating of 10 to Q4 and it automatically ads it to R4 and clears Q4 and totals R4 to an average of 7.5.
This way, with each new survey I can just input each rating to Q4 and have it cleared for the next rating and then have an average rating of all the surveys in R4... we do not need to keep track of how many ratings we are getting, just the average rating.
I have attached the excel sheet if you would like to take a look at what I am trying to do..
I get a text file daily for routed deliveries containing a barcode, name, address and phone number that I convert into csv and then upload it into a route optimization software. after optimization is complete it has assigned packages to drivers and I export it to excel and print. My sorters are having a difficult time reading the addresses on the packages and assigning them to the proper driver, so I am trying to come up with a way.
My idea is that I can import the barcode as a custom field into the route optimizer and then when I export it, that barcode field will still be associated to that order. I want to scan the barcodes of the package into column C which will reference all of the barcodes in B and when it finds the match, it will return the value in A, the driver assigned to that particular package. This way my sorter will just have to scan the packages and throw it into the proper bag for the driver instead of having to visually scan the printed manifest to match.
I'm trying to work out the way of having a cell give a result based on what has been input into two other cells.
Basically I'm doing up a risk management spreadsheet. I need to have in one cell the LIKELIHOOD of an incident, in the second cell, the CONSEQUENCES of it happening and ending up with a RISK MANAGEMENT ASSESSMENT in the third cell.
I want to tie the values of some cells on Sheet 2 to what's put in into corresponding cells on Sheet 1. Example
If Cell B3 on Sheet 1 has the string "B off" put in, Sheet 2 should automatically get "R1" in cell B12, "B off" in cell C12 and "ID123456" in cell D12. If cell B3 on Sheet 1 is empty, B12:D12 should be blank.
