Tagged Questions
0
votes
1answer
27 views
Unable to set range when using XPath with predicate /closed/
Closed the question as it wasn't an error - it just isn't possible
I am trying to return a range that contains all elements in a listobject/xmlmap where a sibling element meets some criteria (a ...
0
votes
0answers
30 views
Diagram Zoom Bar
I have a Excel Sheet (2003) with a Diagram.
Looks like that:
Diagram Source:
So I just wand to zoom the Diagram with a scroll bar or something like that.
Do I have to do it in VBA or with Excel ...
0
votes
0answers
55 views
Change text alignment of category axis data labels
Pulling my hair out a bit with this small problem I'm having. I'm trying to left align the Y-Axis data label text with a bit of VBA code. I've tried 2 approaches:
With ActiveChart
With ...
0
votes
0answers
73 views
Fill out a PDF Form with data from Excel 2003 using vba macros
Is it possible to use Excel 2003 macros to fill out a PDF form?
Basically I have a large pdf file with a few hundred rows of data. I also have an PDF file that is a form. I want each row to be a ...
1
vote
1answer
71 views
Maximum drop-down list/formula length in Excel
I have the following VBA code:
myList = "test"
Range("A1:Z1").Validation.Delete
Range("A1:Z1").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=myList
If my list ...
1
vote
2answers
52 views
Excel 2003 vba chart failed
So im getting a chart failed error and it seems like it is because of my name WBname but i cant figure it out. Had a similar question earlier but since this is a little different i thought i would ...
1
vote
1answer
111 views
Code Error in Excel 2003
I have a code that runs absolutely fine with excel 2007 but its not running with excel 2003. It gives "complie error" and this line turns red.
Selection.PasteSpecial Paste:=xlValues, ...
2
votes
1answer
437 views
VBA create a version 10 pivot table in an xls file via Excel 2007
I need to create xlPivotTableVersion10 pivot tables (Excel 2003 compatible) via a macro that I run on my computer which has Excel 2007 on it.
I run the macro from a a source file. I have another file ...
3
votes
2answers
493 views
VBA hash string
How do I get a short hash of a long string using Excel VBA
Whats given
Input string is not longer than 80 characters
Valid input characters are: [0..9] [A_Z] . _ /
Valid output characters are ...
1
vote
2answers
330 views
Which Excel VBA RegEx expression should I use to count decimal places?
Q: Which Excel VBA RegEx expression should I use to count decimal places?
Desired output
This is a table with some example input strings and the desired output.
INPUT DESIRED OUTPUT
string ...
1
vote
2answers
129 views
VBA have a sub run another sub
how to run a sub stored in another worksheet's module?
worksheet1
sub endDay()
'something here
end sub
worksheet2
sub reCalc()
'something here
end sub
I want recalc to be able to run ...
2
votes
3answers
647 views
Powerpoint VBA: How to get last used column in a row from an Excel sheet
Q: How do I get the last used column in a specific row from an Excel sheet.
While this is a simple task using Excel VBA its more difficult using Powerpoint VBA.
Here is an example of my Excel sheet
...
0
votes
1answer
254 views
Sort/Manipulate Excel data from Access 2003 VBA
I'm trying to run a module in Access 2003 that takes an excel file, if there is info in the cell, then copy the field header and cell in A1, B1, etc... as it loops through each row. There are also ...
1
vote
1answer
76 views
Detect type of calculation via vba
I have a spreadsheet set to manual calculation for time saving (it takes a long time to calculate).
I'd like to offer some way of advising the user whether a calculate needs to be performed or not ...
-1
votes
1answer
43 views
Advise for an Excel 2003 VBA video? [closed]
I would like to learn how to copy cells from an already open excel workbook to another file. I don't just want a code, I want a tutorial. The problem I have is Google is only giving me forums. Can ...
1
vote
2answers
762 views
How can I create text files with special characters in their filenames
Demonstration of my problem
Open a new Excel workbook and save these symbols 設計師協會 to cell [A1]
insert the following VBA code somewhere in the editor (Alt+F11)
execute it line per line (F8)
Sub ...
2
votes
1answer
1k views
VBA Runtime Error : Clear Contents of an Excel sheet
I have written following VBA code in Excel 2003 to clear the contents of an excel sheet;
But it shows
RunTime Error 438: Object doesn't Support this property or Method
Here is my code,
...
1
vote
1answer
97 views
Data In Excel Not Deleted Properly
I am working on an Excel VBA that takes a given timeframe, pulls data, removes extra data outside of certain parameters, and then sorts the data by machine "Riveter 01 - Riveter 22". A chart is then ...
1
vote
2answers
346 views
Excel Filtering and Copying in VBA
I'm working on a VBA script that pulls a range of dates from Access, then filters the data and creates a chart based on the filtered data. The filtered data will be going to a seperate sheet where ...
1
vote
1answer
182 views
How to select the last item in a pivot field using Excel VBA?
I have a pivot table having Pivot field "Date/Time" . I need a macro which selects the last item of the field.
I tried the following code but is not working
Sub Test()
Dim i As Long
i = ...
0
votes
1answer
256 views
Change colour of DataSeries in Excel 2003 VBA
I'm having particular trouble changing the colour of a data series in Excel 2003 using VBA. I've managed to change the colour of the line, but the colour is not correct. I'm very confused and I'm ...
1
vote
1answer
508 views
Merge Multiple Excel Sheets Into Summary Sheet
I wonder whether someone may be able to help me please.
I'm using the code below to allow the user to copy from multiple Excel workbooks and merge the data into a Summary sheet.
Sub Merge()
...
0
votes
1answer
201 views
VBA Excel 2003 protect cells onclick
I have a spreadsheet which has a column on it (say column A). The idea is this column is populated by a user with various scores. Once the user is happy with their answers in column A, I want them ...
0
votes
2answers
337 views
Copy Defined range into next available column on another sheet VBA
Im trying to paste a nominated rage from one workbook to another, my nominated range is called SourceRange and its destination is TargetRange.
However, if data exists in the column c2 on my ...
1
vote
3answers
160 views
Excel 2003(VBA) - Custom Identifier / Function / UDF
I'm currently rewriting a small stock system for my work, and trying to speed up the program as it's dog slow and I have only been doing VBA for 2 weeks now.
In Excel 2003 Edition.
My issue (I ...
4
votes
1answer
423 views
Why use Range.Formula in VBA/VBE for Excel 2003 instead of Range.Value?
I was wondering why in VBA code for Excel 2003 do we even need to use Range.Formula to write a formula to a cell instead of just using Range.Cell? They both write Strings to the cell that become ...
4
votes
1answer
1k views
Dumping variant array to range - VBA excel error 1004
I'm using vba for Excel in order to save data into a array by:
dim allPosts As Variant
allPosts = Range("A2:J5000")
after that I'm changing data in the allPosts array, and than I want to paste it ...
2
votes
1answer
241 views
Excel Run-time error 16 expression too complex
I have this basic code in an Excel sheet
For x = 1 To ThisWorkbook.Worksheets("GRP0").Range("D9:D112").Rows.Count
' do work here
Next x
though when I execute it gives the error
Run-time error ...
0
votes
1answer
314 views
VBA conditional formatting based on contents of merged cells
I have a spreadsheet set up like this:
1 Basic Rota 09:00 13:00
2 Absence S
If you imagine the column labels start above 'Basic Rota' as A, B and C. The Absence cell (B2:C2) is a ...
1
vote
2answers
67 views
Different approach to my situation
on a excel sheet I've got columns, each column represents a weeknumber.
I want to calculate the so-called 4 wk average for each row and for each week and this is the formula I use:
...
0
votes
2answers
94 views
Unnaming a range in VB blows up (Excel 2003, VB 6.5)
I am trying to unname a range if cells if it exists. The code below finds the range with the right name, but explodes at nm.Delete line, without any explanation. How can I fix/debug it?
Thanks!
...
0
votes
1answer
241 views
PivotCaches.Add Mismatch Error - Why didn't it happen before?
I am getting a mismatch error 13 when running the code below. The whole code module has been in use and mostly unchanged for over a year, I haven't changed anything recently, and I am the only user of ...
1
vote
2answers
466 views
FormatConditions and Interior Color
I have an old macro that was working fine on Excel 2003 but creates issues with Excel 2010. The part that causes problems is:
If Not ...
1
vote
2answers
109 views
handle the event of finished editing of the cell value (vba)
Hi in my EXCEL workbook I am using vba. How can I handle the event of loss of focus of the cell or finished editing its value.
1
vote
3answers
171 views
Adding data to a password-protected Sheet
I have a sheet with password. But I need to add some data from vba code. How can I do this? Maybe I can reset password, then add data and then set it again
Me.Parent.Worksheets("Sheet1").Unprotect ...
1
vote
3answers
85 views
Delete open book
I want to create a new work book add a list into it and then save this book. If it is exist I need to delete previous variant and then save current. Here is my code:
Private Sub ...
-2
votes
1answer
67 views
How create a book with table and name column of the table [closed]
How can I create a new work book, in the first sheet create a table and add names of this table?
For example I want create a book with name "Book". In the first sheet with name "Sheet1" create a ...
0
votes
1answer
392 views
Working with file system from vba
I have a folder with 10 excel files + 1 file with macro. I open only macro. How can I get the list of all other files in folder where situated macro file (with vba) and how can I open and close them ...
3
votes
1answer
815 views
VBA get key by value in Collection [duplicate]
Possible Duplicate:
VBA collection: list of keys
Maybe it is a very simple question, but I am new in VBA. I have some data that I want use as Collection(key,value). How can I get key by the ...
1
vote
2answers
90 views
Add info from a form to a table (VBA)
I have list of fields (on a sheet) in EXCEL document and a table. I need to add all info from input fields to this table after click the button. I need to write code of event on VBA. Can someone help ...
0
votes
1answer
559 views
Data Analysis vba on Excel 2010
I had a macro in Excel 2003 that was using the data analysis add-in for vba to do some multi linear regressions.
I recently switched to 2010. The XXXXXXXX\ATPVBAEN.XLA cannot be found and my macro ...
1
vote
2answers
577 views
Can I Get the Source Range Of Excel Clipboard Data?
If the Clipboard contains an Excel Worksheet Range, you can access that Range's Data with the DataObject Object
Can you also find the actual Source Range (ie Worksheet, Row & Column) of that ...
0
votes
4answers
260 views
Reset values in column A based on the value of column B using VBA
I have a sheet that looks like this, but way more complex:
Product count real count
Rake Complete -1 n/a
Rake Head 2 =B3+B2
Rake Handle 2 ...
3
votes
1answer
181 views
Complex pivot-like summary without VBA
Given a set of data like this:
User Item1 Desc. Item1 Sel. Item2 Desc. Item2 Sel. Item3 Desc. Item3 Sel.
UserA Item 1 Yes Item 2 Yes Item 3 ...
0
votes
0answers
249 views
Need to automate QC of excel sheets using VBA
I need to automate quality check on given sheets.
The main steps to be automated are that:-
Products name and row with a particular company need to be in given color say black.
Chart lines and names ...
1
vote
1answer
856 views
How delete a series from an Excel chart using VBA
I'm trying to delete the empty series from a chart in Excel 2003 using VBA. I've seen that others have had this issue in the past and I have tried all methods mentioned in their posts but have been ...
2
votes
1answer
468 views
PivotCaches.add Error 5 - 2003 to 2010 Macros
I have an Excel 2003 .xls file that I am trying to run in Excel 2010. I first saved the file as .xlsm and added the directory as trusted in the Trust Center. I am getting an error code (indicated ...
2
votes
1answer
1k views
Macro - Copy and Paste
My company only uses MS Office 2003 products so I have to stick to it. Because of the nature of my job, i need to use a lot of "copy and paste" function. The source data is mostly from the website and ...
4
votes
1answer
500 views
Incorrect value when counting cells in different versions of Excel
I have seen some SO users run into an issue when trying to use some variation of Cells.Count; the VBA code throws an overflow error in some cases.
For reference, see comments on this answer:
I ...
3
votes
3answers
185 views
is there a way to respond to changes in conditional formatting in excel?
I have an excel sheet that has dde links to real time market data. I use a timer to watch the dde prices every second. Then submit orders when certain conditions are met. I tried an infinite loop with ...
