I know how to get the row number of the cursor in a spreadhseet (Activecell.Row), but how do I get the cursor location in a list object so that I can then insert a new row for the user at that point?
BTW, I am using tables (ListObjects) because when a row is inserted, all of my formulas are automatically inserted.
I am familiar with the codes suggested here: http://www.eggheadcafe.com/software/...-previous.aspx
I move the cursor with Hyper-Links or just by clicking another cell.
I wonder - is there something I can add/do in order to enable returning from ANOTHER sheet !?
On my user form, the cursor blinks in the second text box down.
How do I change it to be blinking in the first (top) text box? This is the first box that data will be entered.
Is this an option in the properties box of the textbox?
i want to use the "Browse for Folder" to select the folder where the files is in eg. E:My DocsGlobal and list the folder location and filenames to table (column I:J), and color them according to every folder location.
I would like the "Browse for Folder" windows to be able to select multiple files instead of just one file a time.
I am trying to develop a spreadsheet that will calculate a cost based on a matrix. I am attaching a sample of the calculation created so far. The end result is in cell M13 and is highlighted in yellow. I kind of layed the formula out in a few different cells, so hopefully it would be easy to follow.
simplify this process with maybe another formula that I might not be aware of, or maybe show me how to get this done in VB code. I think VB code would be the correct way to go just not sure.
I'm currently working on a fairly complex VBA project in Excel 2007. Basically though, I'm downloading information from a site, massaging it, creating a lot of graphs, saving them locally, deleting the chart from excel. I then save a copy of the workbook to a local file, clear the Spreadsheet, and then Do the same for information from another site.
When I had all of the code in one Module and ran it as a macro it ran flawlessly.
Now i've put the information into a Class. And am pretty much running the same process via a UserForm.
The problem I have though is that at any point after I execute the (and please assume that all variables have been declared, because they have)
I have been trying to create a macro in excel to chart a selection of data and to output the chart on the active sheet where the data was taken (as opposed to a named sheet). So basically, I have about 300 worksheets with data, and I would like to have a button on each page that automatically charts that data when clicked, and outputs the chart to the page where the macro was clicked. However, I have not been able to figure out a relative reference that will allow me to make the LocationasObject reference simply the ActiveSheet as opposed to a specifically named sheet. See my code below, which references an output to a worksheet called "Charts". Right now, all of my charts are outputting to the sheet called "Charts", as opposed to the active sheet.
Sub ConsDiscChart()
ActiveCell.Offset(29, 11).Range("A1").Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Range("A1:B1").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveCell.Offset(0, -1).Range("A1:C24").Select
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.Location Where:=xlLocationAsObject, Name:="Charts"
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
End Sub
I am working with a VBA userform and several textbox's, setting SetFocus and or TabIndex doesn't leave the box ready to accept input and there is no cursor shown to indicate it is ready to accept input.
View 2 Replies View RelatedI have pulled a SharePoint list into my workbook. The list object (table) is still linked to the SharePoint list, as I'd like to synchronize it later on. I have filtered it with an autofilter. I'd like to delete all of the visible rows. I have tried a billion things to no avail. I have been searching Google for hours now. None of the examples work.
View 5 Replies View RelatedAfter I run Hlookup on a product, I would like two variables, Row and Col, to assign the actual row and column of the spreadsheet to the location in the table. I have a small worksheet/program that I have attached, what I am trying to do. The program works, but my code is very long for the little bit of information.
Sales.xlsm
I need to assign headers to a table according to the file name and location. I have attached the example spreadsheet. Sheet1 contains the table with the data and Sheet2 assigns the headers to each file and location. The code is skipping headers and I can not figure it out.
Macro Example.xlsm
I am trying to achieve can not be based on a range of rows or cells it must as this data is imported from a forecasting application and the location of the targets may change.
Perhaps an offset to the current selection can be used some how, but have a look-see if you know where Im coming from.
'I have used this to find a target in a data range.
Dim r1 As range
Public Sub FindDataIn Range(r As Range, target As Variant)
Set r1 = r.Find(target)
If r1 Is Nothing then
Msg Box target & " was not found"
Else
r1.Select
End If
End Sub
'Then I inserted and Named this procedure to find the data on various assumptions or targets - SUCH AS THE VALUE "14306".
FindDataInRange ActiveSheet.Range (A1:A226), "14306"
Selection.EntireRow.Select
Selection.Copy
Selection.Insert Shift:=x1Down
Selection.EntireRow.ClearContents
Then I write this again for another Target such as 14307 and it repeats
The result is that it finds the target cell I get an empty row above the two rows that contain that target. (LET ME EXPLAIN WHAT I MEAN BY TWO ROWS)
The thing is there are two rows containing 14306 in that range and the range is sorted ascending so that they are positioned one under the other. Each row has different forecast totals beside this number because one is an export SKU and one is a Domestic.
What I want to do is combine the two rows as one with one row of forecast totals for the number rather than two.
Like this:
14306big Widget Domestic 26 89 (This is combined as a new row)
Instead of this:
14306big Widget Export 12 14 (These 2 rows are then deleted)
14306big Widget Domestic 14 75
Also the Forecast totals run across 12 columns (one for each month) and then there is a column for year totals of each row that needs to remain the same.
Most important this here is this cannot be based on a range it must as this data is imported from a forecasting application and the location of the targets may change.
I have a table of information with location codes as the column headers. Each location has from 1 to 6 codes associated with it.
I want to create a new table that displays the information by location instead of code, i.e. adding all of the codes for a location into one column for that location.
I'm hoping the example will make this clearer.
On the main page, I am trying to add in the wa column all of the columns in the raw page that have a code associated with wa as the header.
To make this more complex, I can't use vba on this one.
the only thing I've got so far is a very long, very complex formula that adds together numbers generated from index/matching each entry in the second table.
something like this.
(edit changing 1:1 to $1:$1)
=IF(VLOOKUP(B$1,lookup1,2,FALSE)=0,0,INDEX(sample.xls!main_table,ROW(),MATCH(VLOOKUP(B$1,lookup1,2,FALSE),raw!$1:$1,0)))+IF(VLOOKUP(B$1,lookup1,3,FALSE)=0,0,INDEX(sample.xls!main_table,ROW(),MATCH(VLOOKUP(B$1,lookup1,3,FALSE),raw!$1:$1,0)))+IF(VLOOKUP(B$1,lookup1,4,FALSE)=0,0,INDEX(sample.xls!main_table,ROW(),MATCH(VLOOKUP(B$1,lookup1,4,FALSE),raw!$1:$1,0)))+IF(VLOOKUP(B$1,lookup1,5,FALSE)=0,0,INDEX(sample.xls!main_table,ROW(),MATCH(VLOOKUP(B$1,lookup1,5,FALSE),raw!$1:$1,0)))+IF(VLOOKUP(B$1,lookup1,6,FALSE)=0,0,INDEX(sample.xls!main_table,ROW(),MATCH(VLOOKUP(B$1,lookup1,6,FALSE),raw!$1:$1,0)))
It works, but its horribly ugly, and if the number of locations goes higher (we could be looking at going to ten location codes for one of our locations) then I'll actually exeed the 1000 characters per formula limit!
I am trying to use HLOOKUP to find a location in another sheet and display all the names in that specific location. Also can change location at will and will display the names for that location.
I have attached a spreadsheet that kinda has what I want. It does not have to be HLOOKUP but that is all I know, Hlookup and vlookup.
Sorry that I'm very new in Excel VBA coder. And, for this topic, I don't even know how to start. I want to make a MS Excel database of a numerous files. This database must be consist of Filename, Location, and it's attribute (let's say updated date, size, hidden status)
View 2 Replies View RelatedI have some spreadsheets that rely on data being pulled in from external querys (from an SQL database).
the below line of code is part of a Workbook_Open() sub, which refreshes the data.
Selection.QueryTable.Refresh BackgroundQuery:=False
I have just got my hands on acopy of Excel 2007 (Yes!!!), but when i open my sheets my code now fails
Ater some messing around Ihave found that if I add ListObject" as below, it now works.
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
trouble is it doesn't work for the rest of the office, who didn't get Excel 2007
is it possible for me to have this so that it works for both flavours of Excel?
In my example you will see my name list that changes with a array to data validation. Seems like you can find ways around things most of the time but this time
View 4 Replies View RelatedI need the sum of a column in a table. In the sheet I am using "=SUBTOTAL(109;[Total])", but I need the absolute total in VBA. How is that possible?
View 1 Replies View RelatedI am trying to rename a text box, but it doesn't let me. The naming convention is fine, as it works with others. But it thinks that name is taken or something. So how can I see the names of all objects on a sheet?
View 9 Replies View RelatedI have a workbook with three sheets:
-"DB" database sheet containing multiple tables (20 to be exact, named as "CityA", "CityB", etc.)
-"Threat Data" reference sheet containing a "City_Ref" table with the list of tables names in the "DB City ID" column and the unique city name assigned to each table in the "City" column (the city name is populated in a "City" column of each table in the database sheet).
-a dashboard sheet containing an interactive userform for which to populate the database tables.
In this userform, there are two comboboxes:
-a "CbxCity" combobox which lists all the city names from a "City" column in the "City_Ref" reference table
- a "CbxAsset" combobox which should automatically list all the values in the "Asset" column of the selected city table based on "CbxCity"
My issue lies in filling "CbxAsset" based on the selection in "CbxCity" ; how do I dynamically select a ListObject name based on a selection?
The code I am working with is:
[Code] .....
In the CbxCity_Change() sub, I am not sure how to name the ListObject and my code currently gets an error at r = Me.CbxCity.Value
Which is strange because that is showing the selected city name when I run the cursor over the bug.
I'm trying to write a remove duplicates sub that can be passed the worksheet name and columns on which to check. It's pasted below. Above the key line is a commented out line of code that worked. So it works to pass theh worksheet, but I'm hung up on how to pass varying columns to it.
Sub RemoveDuplicatesSub(wksht, cols)
'Remove duplicates.
'Assumes that the data range is in a table
'Assumes the header row starts at row 7
Dim WS As Worksheet
Dim TableName As String
Set WS = Worksheets(wksht)
TableName = WS.ListObjects(1).Name
'WS.Range(TableName).RemoveDuplicates columns:=Array(3, 4, 5, 6, 7, 8, 9, 10), Header:=xlYes
WS.Range(TableName).RemoveDuplicates columns:=Array(cols), Header:=xlYes
End Sub
I have been working on large project using Excel VBA for several days. My code seems to be working correctly, but I have more to do and now, when I drag an object from the toolbox onto a UserForm, the object is not added to the list of objects on the form. If I go back to versions of the project that I was working on several days ago, there is no problem. If I run "Workbook Rebuilder", the objects that I have dragged onto the form are then added to the object list, but I still can't add new objects to forms from within the VBA editor. Is the project corrupted, or is there some other explanation, and are there any fixes? The code runs about 50 pages, and there are over 20 forms, so redoing from scratch is only a last resort option.
View 3 Replies View RelatedI require a bit of code that locates where the excel file is stored - it then searches that directory folder for all file names. Any file names with an extension *.hm for example are listed in column A, while their relevant 'Dates of last Modification' are listed in Column B.
Every time the spreadsheet opens it should ask the user if he/she requires a re-search of this directory and update of any file names accordingly. NB: if a file name is deleted from the directory, the file name should remain in the spreadsheet.
I have a worksheet size roughly 100 * 400 cell. there is roughly 100 cells filled with RED color and with different value.
When i used FIND ALL format - RED, I can locate the cell location and the cell value. And I wish to copy these 2 info onto a new sheet so that on the new sheet, I can create a hyperlink and easily access to the location of the RED cell.
However, in FIND and REPLACE, i have a list of results on what I want. But i cannot copied the information out. So is there anyway to copy it out or to achieve what i want?
For eg.
RED value
cell
50
A3
87
D71
251
AQ5
and so on.
I have a set of accounts (general ledger accounts) that the accounting group posts expenses to. every once in awhile a new account is added. This is captured through a pivot that i have built (sheet A).
on another sheet (sheetB) i want to display the accounts that are shown in the pivot so that i can forecast their future activity.
What i am trying to figure out is how can i make the list on Sheet B change when new accounts are added to the pivot on Sheet A (without simply referencing the pivot table making one cell equal the other)? I am not looking for a data validation pull down, i want a full list of the accounts.
I'm getting the following error:
"Object library invalid or contains references to object definitions that could not be found"
I wasn't getting that error last night and I'm not sure what I may have done to cause this error.
It seems to be cause by code running on one sheet of my workbook, but I'm not really sure about that. I'm still a bit of a novice at VBA.
I'm using Excel 2002 SP3 and I'm running MS XP Home as my OS.
Do you have any ideas what can cause this error and/or how to trace down the offending objects/code?
I keep running into this error when I try to create a pivot table.
The debugger highlights the following line:
I'm talking about the little black box that appears anytime you put the mouse on a cell, or drag the mouse around a group of cells - mine doesn't show up. I can still edit the cell, but I have to guess about which cell I am on.
how to make my dear cursor reappear?
I try to move the cursor using the keyboard arrows it stays on the cell it is on and the whole spreadsheet page moves across the screen in the direction of the arrow. So how do I restore cursor movement to the arrows.
View 3 Replies View RelatedHow do I make the cursor jump down to the next row. Example: once I entered value on H2, I would like the cursor jump down to A3 then once I reach H3, the cursor would jump down to A4 and so on.
View 5 Replies View Related