How to program a macro that moves my chart as I scroll through my
I have a very large spreadsheet. In this spreadsheet, I have a series
of indicators. I have a small macro that bases the chart values off
of my current selection. What I would like to do is program my chart
to move as I scroll. So as I scroll down the spreadsheet, the chart
moves with my current view. Does anyone know how to do this?
Thanks Tag: autosort left to right Tag: 998050
Change all label controltiptexts in userform
Hi guys,
This is driving me insane!!!
I'm trying to have the text of all of the controltiptext properties on
a userform to be the same as the caption property of the label
(there's loads of labels).
It seemed like it should have been easy, but after much trial and
error I've managed to come up with the following (which I don't quite
follow) and feels so close, but I'm stumped as to get the caption in
there:
Sub Change_Labels_UserForm()
Dim oVBProj, oVBComp As Object
Dim ctl As Control
Set oVBProj = ThisWorkbook.VBProject
On Error Resume Next
For Each oVBComp In oVBProj.VBComponents
If oVBComp.Type = 3 Then
For Each ctl In oVBComp.Designer.Controls
If Left(ctl.Name, 5) = "Label" and ctl.ControlTipText = ""
Then ctl.ControlTipText = 'caption name
Next
End If
Next
End Sub
Any ideas?
Cheers,
JF Tag: autosort left to right Tag: 998045
Insert Row After Series
I think this is possible but I'm not certain of the way the code would look.
I have an extract that comes to me in Excel format and I have a Macro that
runs through the Excel file reformatting it. The extract will have several
hundred rows.
Column I has a State description (I believe there are a couple dozen
descriptions) and what I need to do is add a blank row after each state.
Example ...
Pending
Pending
Pending
In Process
In Process
Active
Active
Active
would become
Pending
Pending
Pending
In Process
In Process
Active
Active
Active
I guess what I would like would be to add code to the existing Macro that
basically says everytime the value in column I changes add a row. Any ideas.
I hope this makes sense. Tag: autosort left to right Tag: 998041
Check for NumberFormat = DateFormat
Hi all,
is there a simple way to check if a Range.NumberFormat returns a Date
format ? To be sure I am not misunderstood: the Cell-Values might be
empty but the cells are formatted to a date format. by default it might
be dd/mm/yy or similar but could also return "[$-409]d/m/yy h:mm
AM/PM;@" or sth.
what I am searching for is a simple way to check if the
range.numberformat fits to any possible dateformat. best case would be
"If Range.NumberFormat = IsDateFormat then..." but I cannot seem to find
something like that. Is there an easy solution for that ?
thanks for any kind of hints.
Joe. Tag: autosort left to right Tag: 998039
Input the filename into Excel Sheet
Dear Experts,
I am asked to input the fillename as text into the Excel sheet. These files
are PDF formated. Like 19347-Autoline-6001004.pdf,it is thousands files. Is
anybody help me to provide me a solution to "read" these file names
automatically into my worksheets like:
A B C
1 19347 Autoline 6001004
2. 19348 autoline 5001045
....
Eager to know the solution!
Li Jianyong Tag: autosort left to right Tag: 998035
find
Is there any way to program a workbook so that I am able to click on any
individual cell in a workbook, which contains multiple sheets, that would
then serach the entire workbook and automatically locate/find any other rows
in the entire workbook that contains information identical to that cell that
I originally selected? Tag: autosort left to right Tag: 998032
Referencing Checkbox Name
I was wondering how I could reference the name of a checkbox and assign it to
the value of a cell.
To give an example of what I am looking for:
I have 5 checkboxes on a sheet and the names of these checkboxes will
eventually change. I would like call A1 to return the name of check box 1,
cell A2 to return the name of check box 2, and so on.
Any help would be greatly appreciated.
Thanks!
Chad Tag: autosort left to right Tag: 998029
#NAME? in cell when opening a Workbook
I have some Excel VBA code that I saved as a .XLA Add-In file. I then have
this XLA set as an Add-In inside of Excel. If I enter my UDF into a cell, it
works correctly and returns the values that I expect.
Every time I re-open a Workbook that contains calls to my UDF in the Add-In,
all cells that call the UDF have " #NAME? " in the cell. Excel goes through
and recalculates these cells and all is correct and #NAME? is replaced with
the output of the UDF for the cell.
Why does Excel have to resolve the reference to this Add-In each time it
opens? Tag: autosort left to right Tag: 998025
importing another file
Hello every one,
I have to run a report every monday to check on project status. the report
(file1)has two blank rows in between each project. I have a marco that
deletes blank rows. I also have a file that has my calculations in columns ac
thru columns ao. In the past i would just copy all the columns in File 1 in
to File 2. is there a way I could automate this?
Thanks Tag: autosort left to right Tag: 998019
Set Pivot-Table Visible Items
With ActiveSheet.PivotTables("AssetPT").PivotFields("Cost ctr")
.PivotItems("12476").Visible = True
.PivotItems(DataArray(X, 1)).Visible = True
end with
Why would the line with "12476" work and the line immediately below, where
DataARray(x,1) equals "12476" give me this error:
Error (1004) Unable to get teh pivotItems property of the pivotfield
class????? Tag: autosort left to right Tag: 998015
Create range names with names in column heading
Hi,
Need some help.
I have a sheet that has column headings along row 1. Would like to create
range names with each column heading as the name. The number of rows will
increase.
Any suggestions? Tag: autosort left to right Tag: 998009
use only numbers and '/'
I have a textbox in a userform that I want to only accept numbers and '/' to
enter dates. How do I do that?
Thanks Tag: autosort left to right Tag: 998008
Find entries in one column in another
Hi,
I have been trying to figure out a solution to this problem for days now and
it is really bugging me...
I have two columns with descriptions in. One column is part of a 'catalogue
list' and one column is part of a 'things that have been ordered list'. For
each item that is ordered the buyer needs to decide whether the item which
they are ordering is on the catalogue or not. This is however not always done!
This is where I come in. It is my job to search the list of items that have
been ordered and using the description of the item (sometimes 10 words long)
decide whether it is already on the cataolgue. This can be very time
consuming and I was wondering if there was a VBA code which could speed up my
manual process by comparing the two descriptions columns and matching up
similar results.
An example could look like...
'Things that have been ordered list'
description
**msc Breaded Fish Fillet Squares 70g 1x36pk
'Catalogue list'
description
Fish fillets 1x36pk, 70g
Any help would be much appreciated...I am losing sleep over this!! Tag: autosort left to right Tag: 998002
Andy Pope's button image editor; pasting icons to sheet then toolbar- why does image quality degrade?
I used Andy Pope's add-in to save my custom button icons as .bmp files.
To use them in my add-in, instead of trying to load them from the .bmp files
directly, I used insert/image to put each .bmp on a worksheet in my add-in.
I then use code to paste them to my new toolbar.
The problem is that I seem to have quality problems in (at least) two
places; when I insert a .bmp and enlarge it for analysis, the picture is
similar to the original, but blurry (I'd expect it to just show a large
16x16 block of colors). Then when I paste it to the toolbar- then copy that
image back into Andy Pope's editor, the end result shows that the image had
further degraded.
I'd like a clean (and transparent) .bmp on my final toolbar, but many google
searches have turned up nothing.
Is there a preferred approach to ensure that the button image quality
remains stable/clean all the way through to the final toolbar?
Thanks!
Keith Tag: autosort left to right Tag: 997989
Formula bar is hidden, how do I unhide it?
Somehow I have hidden the forumla bar and cannot make it visible again.
If you know how to "unhide" it, please respond. Tag: autosort left to right Tag: 997988
Textbox value
I have a textbox on a userform to input dates. How do I format the textbox
to only accept dates in the form of mm/dd/yy?
Thanks Tag: autosort left to right Tag: 997986
Median of all combinations
Hi,
I have 5 numbers (can be with decimals) in column A (A1:A5). For these
numbers, I want all combinations as well as the median of these
combinations.
I'm pretty far already. However, the median seems to be calculated
wrong.
Who can help me?
Thanks,
Andreas
Sub test()
ListCombos Range("A1:A5"), 3, "C:\Q1_3er.csv"
End Sub
Sub ListCombos(r As Range, ByVal m As Long, sFile As String)
' lists the combinations of r choose m to file sFile
' r is a single-column or single-row range
Dim ai() As Long
Dim i As Long
Dim n As Long
Dim sOut As String
Dim sOutMedian As String
Dim iFF As Integer
If r Is Nothing Then Exit Sub
If r.Rows.Count <> 1 And r.Columns.Count <> 1 Then Exit Sub
n = r.Count
If m < 1 Then Exit Sub
If m > n Then m = n
iFF = FreeFile
Open sFile For Output As #iFF
ReDim ai(1 To m)
ai(1) = 0
For i = 2 To m
ai(i) = i
Next i
Do
For i = 1 To m - 1
If ai(i) + 1 < ai(i + 1) Then
ai(i) = ai(i) + 1
Exit For
Else
ai(i) = i
End If
Next i
If i = m Then
If ai(m) < n Then
ai(m) = ai(m) + 1
Else
Exit Do
End If
End If
' catenate and write to file
sOut = vbNullString
Call Sort(ai)
sOutMedian = median(ai)
For i = 1 To m
sOut = sOut & r(ai(i)).Text & ","
Next i
Write #iFF, Left(sOut, Len(sOut) - 1), sOutMedian
Loop
Close #iFF
End Sub
Sub Sort(Arr() As Long)
Dim Temp As Double
Dim i As Long
Dim j As Long
For j = 2 To UBound(Arr)
Temp = Arr(j)
For i = j - 1 To 1 Step -1
If (Arr(i) <= Temp) Then GoTo 10
Arr(i + 1) = Arr(i)
Next i
i = 0
10 Arr(i + 1) = Temp
Next j
End Sub
Function median(Arr() As Long)
Call Sort(Arr)
If UBound(Arr) Mod 2 = 1 Then
median = Arr(Int(UBound(Arr) / 2) + 1)
Else
median = (Arr(UBound(Arr) / 2) + Arr(Int(UBound(Arr) / 2) +
1)) / 2
End If
End Function Tag: autosort left to right Tag: 997985
compare oldvalue and new value of a cell
Hi All,
how to write a code to compare the value of a cell
before it's being updated by user.If the new value
is different with the new value,I want the cell
background colour become different.But since
I have many sheet in my workbook,I want the code
become public and I try to keep the sheet clean
from any vba code.
Thank's
Shiro Tag: autosort left to right Tag: 997979
Get reference to a file opened in another instance of XL
Using Office 2003;
Generic:
I need to loop thru all open MS-Excel files in another instance of XL with
the object in mind of getting a reference to a particular file opened as a
template. I can positively identify the template file by the contents of
certain cells in the template.
Specific:
The user will be opening an Oracle Financials, Fixed Asset (CPR) ADI
spreadsheet which always opens in a new instance of XL. I need to get a
reference to this spreadsheet and populate it with data from an array.
I can do all of this just fine, but it's the reference to the other instance
that is a challenge. Any help would be appreciated. Tag: autosort left to right Tag: 997977
Slow procedure
I have modified a procedure I downloaded from Debra Dalgleish's site and show the detail
as below. Basically it looks at a range of values then creates a named worksheet for each
of these values if that worksheet does not already exist. If it does exist it just clears
some ranges and copies in some filtered data. I would be grateful if someone could have a
quick look through to see if I have put in anything in such a way that it would really
slow the operation of the procedure. Don't get me wrong, the procedure does exactly what
it is ecpected to do , it just seems to take a bit of time and I just wonder if there is
anything slowing it. I am sorry I have notes above each operation as I am not the sharpest
pencil in the box when it come to programming and I need to keep track of what I am trying
to do. Don't spend a lot of time on it, as I say it works and is liveable with.
Sub ExtractFields()
Dim ws1 As Worksheet
Dim wsNew As Worksheet
Dim rng As Range
Dim r As Integer
Dim c As Range
Set ws1 = Sheets("FieldMaster")
Set rng = Range("DatabaseList")
Application.ScreenUpdating = False
r = Sheets("Entries").Cells(Rows.Count, "A").End(xlUp).row
For Each c In Sheets("Entries").Range("A12:A" & r)
' check if sheet exists
If WksExists(c.Value) Then
'Clear existing sheet areas if sheet already exists
Sheets(c.Value).Range("B12:E15").ClearContents
Sheets(c.Value).Range("B23:E28").ClearContents
Sheets(c.Value).Range("J12:N15").ClearContents
Sheets(c.Value).Range("J23:N28").ClearContents
' run advanced filter to get organic applications
rng.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets(c.Value).Range("Q1:Q2"), _
CopyToRange:=Sheets(c.Value).Range("C11:E11"), _
Unique:=False
Else
'If sheet does not exist add it
Set wsNew = Sheets.Add
' enter after last sheet
wsNew.Move After:=Worksheets(Worksheets.Count)
' name the sheet
wsNew.Name = c.Value
' copy template to new sheet
Sheets("FieldBase").Cells.Copy Destination:=wsNew.Range("A1").Cells
' Enter field name into FieldMaster for soils copy
Sheets("FieldMaster").Range("AA2").Value = wsNew.Name
' Copy base soil to Soils sheet
Range("SoilBase").Copy Sheets("Soils").Cells(Rows.Count, 1).End(xlUp)(2)
' enter sheet name in reference cell
wsNew.Range("B2").Value = wsNew.Name
' put field name into filter criteria to allow for alphanumerics
wsNew.Range("Q2").Formula = "=""=" & wsNew.Range("B2").Value & """"
' run advanced filter to get organic applications
rng.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=wsNew.Range("Q1:Q2"), _
CopyToRange:=wsNew.Range("C11:E11"), _
Unique:=False
End If
Next
ws1.Select
End Sub
Function WksExists(wksName As String) As Boolean
On Error Resume Next
WksExists = CBool(Len(Worksheets(wksName).Name) > 0)
End Function Tag: autosort left to right Tag: 997974
OldCell colour
With below code,I want turn the OldCell colour to
another colour if the cell's value was updated by user,
if it's not updated the colour is xlColorIndexNone,how to
modify the code?
Thank's in advance.
Option Explicit
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal
Target As Excel.Range)
Static OldCell As Range
If Not OldCell Is Nothing Then
OldCell.Interior.ColorIndex = xlColorIndexNone
End If
Target.Interior.ColorIndex = 6
Set OldCell = Target
End Sub Tag: autosort left to right Tag: 997970
Problem setting each parameter in an Array = Nothing
I want to reset each parameter in an Array = 0 or " ". Why can I not make
myDes = Nothing. I am getting an error below. If I set myDes = 0 everything
works fine, but it will not work when myDes = Nothing
Sub ResetVariables()
Dim myBOM_Des As Variant
Dim myDes As Variant
' row location of part on "Parts List"
myBOM_Des = Array(AlumFace_Row, Texture_Row, Primer_Row, Paint1_Row,
Paint2_Row, Paint3_Row, Paint4_Row, Vinyl1_Row, _
Vinyl2_Row, Vinyl3_Row, Vinyl4_Row,
ClearVinyl_Row, Ink_Row, Plastic_Row)
' sets row locations to Nothing
For Each myDes In myBOM_Des
ERROR => myDes = Nothing
Next myDes.
End Sub
--
Cheers,
Ryan Tag: autosort left to right Tag: 997968
Mutually Exclusive Option Boxes
I need to set up 3 option boxes that are mutually exclusive.
When I click on one option box the other two should be unchecked
etc.etc.
How do I go about doing this - I had a look through the forum but I
can't seem to find a solution.
Thanks Tag: autosort left to right Tag: 997963
Why doesn't this work?
Hi All,
The following code snippet is invoked from my personal.xls workbook.
What l am attempting to do is select any excel file in any directory
and then create a .xls file for each worksheet in the selected
workbook into a 'TEMP' directrory
The code is failing at the 'sh.Copy' line. Can anybody tell me what is
wrong please?
FileNameOnly & DirOnly are simply functions that l have used many
times to extract the relevant information from the string returned by
GetOpenFilename
Additionally l need to add some code to check if the 'TEMP' directory
has already exists, any ideas, example code would be gratefully
appreciated.
Sub CreateXLFiles()
Dim afile As String 'Source workbook to be rebuilt
Dim adir As String 'Directory of sheet files
Dim sh As Worksheet
afile = Application.GetOpenFilename(, , "Select the source
file", , False)
Application.ScreenUpdating = False
adir = (DirOnly(afile) & "\" & FileNameOnly(afile) & "-TEMP")
MkDir adir
Workbooks.Open afile, UpdateLinks:=False
For Each sh In Workbooks(FileNameOnly(afile)).Worksheets
sh.Copy <<<<<<<<<<<<<<<ERROR HERE
ActiveWorkbook.SaveAs adir & ActiveSheet.Name & ".xls"
ActiveWorkbook.Close SaveChanges:=False
Next
Workbooks(FileNameOnly(afile)).Close SaveChanges:=False
End Sub
Regards
Michael Tag: autosort left to right Tag: 997950
Printing style
I write the C# module that open the workbook and send it to printer.
Is there any posibility to detect that the worksheet was designed to print
in Landscape or Portrait.? Tag: autosort left to right Tag: 997940
PLEASE HELP WITH AN SPECIAL AUTOFILL
A good day !
I have in a sheet in entyre column BD , 1423 cells with array
formulas ;
the cells with formulas are from range BD91 to BD65536 , in every 46
row :
BD91 , BD137 , BD183 , BD229 , BD275 , BD321, BD367 , BD413 , BD459
BD505, BD551 , BD597 , BD643, BD689 , BD735 , BD781 , BD827 , BD873
BD919 , BD965 , BD1011 .................etc...............BD65319 ,
BD65365 ,BD65411
BD65457 , BD65503
With usual macro I have 1423 lines in macro to do this autofill :
Range("BD91").AutoFill Destination:=Range("BD91:BD136"),
Type:=xlFillDefault
Range("BD137").AutoFill Destination:=Range("BD137:BD182"),
Type:=xlFillDefault
Range("BD183").AutoFill Destination:=Range("BD183:BD228"),
Type:=xlFillDefault
Range("BD229").AutoFill Destination:=Range("BD229:BD274"),
Type:=xlFillDefault
Range("BD275").AutoFill Destination:=Range("BD275:BD320"),
Type:=xlFillDefault
Range("BD321").AutoFill Destination:=Range("BD321:BD366"),
Type:=xlFillDefault
Range("BD367").AutoFill Destination:=Range("BD367:BD412"),
Type:=xlFillDefault
Range("BD413").AutoFill Destination:=Range("BD413:BD458"),
Type:=xlFillDefault ........
.........................................etc..............................................................................
Range("BD65411").AutoFill Destination:=Range("BD65411:BD65456"),
Type:=xlFillDefault
Range("BD65457").AutoFill Destination:=Range("BD65457:BD65502"),
Type:=xlFillDefault
Range("BD65503").AutoFill Destination:=Range("BD65503:BD65536"),
Type:=xlFillDefault
My goal is to get *THE BEST SPEED* for this autofill in this entyre
column BD ;
Please very much to provide me this kind of macro , which shall give
me the MAXIMUM
SPEED to autofilling the 1423 cells in entyre column ;(I use excel
2003 ).
Thanks very much for your time Tag: autosort left to right Tag: 997932
Copy From HTML prgramme figures
I have copied a table (values) from a HTML program but when it come to add
only it is adding half of it the rest when trying to add that is not adding,
i have seperatly try to add those are not adding it show #VALUE!
--
VJC Tag: autosort left to right Tag: 997926
Detecting the change of a certain cell
Dear All:
Is it possible that someone can kindly tell me how to detect the change of a
certain cell?
environment is VSTO + VS 2008 + EXCEL 2007.
the Globals.sheet1.Calculate += is successful
but the Globals.sheet1.Change doesn't work.
Could anyone provide a sample?
FYI the code is written within a window Form.
Best Regards,
Fan Tag: autosort left to right Tag: 997923
If one range changes, update the other
Hi,
With some help from user OssieMac,
I was able to get the code to this point, but I think I may need some help
to get to the next step.
For the sake of this test, I have two named ranges on one worksheet:
Range("Sh1BWTest") and Range("Sh2BWTest")
When I insert a row into Range("Sh1BWTest"),
I need the row range in Range("Sh2BWTest") to also adjust and update..
The code is almost there, I just can not get that last part.
Any help would be appreciated.
Thank you
ML
Private Sub Worksheet_Change(ByVal Target As Range) '*** This part works well
Dim isect As Object
Set isect = Intersect(Target, Range("Sh1BWTest"))
If Not isect Is Nothing Then
Application.EnableEvents = False
'Clear data from Sh2bWTest in case the Sh1BWTest range is smaller
Range("Sh2BWTest").ClearContents
MsgBox "Cleared" 'Confirm the clear was sucessful
Range("Sh2BWTest").Value = Range("Sh1BWTest").Value
'Select and rename range
Range("Sh2BWTest").Select
ActiveWorkbook.Names.Add Name:="Sh2BWTest", _
RefersToR1C1:=Selection
End If
Application.EnableEvents = True
'Range("A1").Activate
End Sub Tag: autosort left to right Tag: 997920
Excel crashes
Hi,
I am using Excel 2007 on Windows XP.
On the sheet I have a button (form button) that executes the macro below.
The macro runs fine, the new workbook (myTemplate1.xls) opens up OK.
The problem occurs when I close the workbook (myTemplate1.xls) Excel
crashes.
What am I doing wrong?
By the way I have been using myTemplate (opening it manually) for years
without a problem. It is only when I open it using the macro below that I
have this problem.
Thanks for any help
Fred
Sub Button432_Click()
Dim PartNo As String
PartNo = Trim(Sheet1.Range("C8").Value)
If PartNo <> "" Then
Workbooks.Add Template:="\\myServer\myDrive\myTemplate.xlt"
End If
End Sub Tag: autosort left to right Tag: 997919
Email with Notes
I am trying to setup to email a spreadsheet thru notes, I have everything
worked out EXCEPT for the send to I have tried the following
emailsendto=emailadd
emailadd is a range in a spreadsheet (email b2)
the format is
"username@company.com,","username2@company.com,","username3@company.com"
but it is only sending to the first name
Now I could set the range to B2:b11, but not sure of the syntax
any help would be appreciated Tag: autosort left to right Tag: 997918
Compile Error: Procedure too large
I am using the:
Private Sub UserForm_MouseMove(ByVal Button As Integer, ByVal Shift As
Integer, ByVal X As Single, ByVal Y As Single)
routine to run a series of macro's, but not i get the 'Too large error'.
I was thinking if i break up the code and place it into various Modules, and
using a 'Call" procedure to run the macro with this procedure instead.
However when i tried this it does not run.
Can i remove the 'Private' and use a 'Public' to get it to work? Tag: autosort left to right Tag: 997917
If, then, for a long time
I've asked several questions lately, and I'm sorry about that. I have a
series of formulas that I could probably put into a very large macro, but I'm
sure there's a way in VBA, but I can't figure it out. I have a very large
spreadsheet to work with, but the formulas on most of them are either the
same of similar. In one section I have a table that is renewed monthly. The
table looks like this:
Term Value
1 13905
2 12920
3 12015
4 11455
5 11095
6 11060
7 11200
8 11010
9 10580
10 10160
11 9705
12 9210
13 8665
14 7985
15 7375
16 6990
17 6730
18 6690
19 6745
20 6585
21 6280
22 5980
23 5660
24 5320
In addition there is a row of numbers that relate to this table that also
changes monthly (it's used to keep track of the market value of a vehicle).
The rows of data look like this:
Aug-07 $18,848
Sep-07 $18,320
Oct-07 $17,792
Nov-07 $17,264
Dec-07 $16,736
1 Jan-08 $16,208
2 Feb-08 $15,680
3 Mar-08 $15,152
4 Apr-08 $14,623
5 May-08 $14,095
6 Jun-08 $13,567
7 Jul-08 $13,039
8 Aug-08 $13,905
9 Sep-08 $12,920
10 Oct-08 $12,015
11 Nov-08 $11,455
12 Dec-08 $11,095
13 Jan-09 $11,060
14 Feb-09 $11,200
15 Mar-09 $11,010
16 Apr-09 $10,580
17 May-09 $10,160
18 Jun-09 $9,705
19 Jul-09 $9,210
20 Aug-09 $8,665
21 Sep-09 $7,985
22 Oct-09 $7,375
23 Nov-09 $6,990
24 Dec-09 $6,730
25 Jan-10 $6,690
26 Feb-10 $6,745
27 Mar-10 $6,585
28 Apr-10 $6,280
29 May-10 $5,980
30 Jun-10 $5,660
31 Jul-10 $5,320
The August 07 amount is when it was put in service. We didn't really start
keeping track until a few months later, which is next to the number 1. There
is another cell that I put a date for the end of the month in, and I have
several other cells that use that date in a vlookup table using the dates you
see above (I've had to turn those formulas into VBA code, but that's another
issue).
Here's the deal. When it's July 2008 a formula is in place to move make
various calculations, and the August 2008 cell should equal the first number
in the first table above (in this case 13,905). In August 2008 I need the
formula to carry down from July, and for September to then equal the first
number in the table, October to equal the second number, etc. Is this
something I can set up in VBA to do? I appreciate the help.
--
Message posted via http://www.officekb.com Tag: autosort left to right Tag: 997907
Sheet sub to fire after DV selection is changed
I'm using the sheet sub below to auto-scroll horizontally
In C3 is a formula: =ADDRESS(5,MATCH(C2,G5:HJ5,0)+6,4)
In C2 is a DV which selection feeds into C3 for the resulting cell address
Despite calculations set to auto mode, I find that after I select the DV, I
still have to click inside C3, then press ENTER before the auto-scroll sub
will work.
How could the sub be amended to work straight off after the DV selection is
changed?
Thanks for insights
----------
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Handler
If Target.Address = Range("C3").Address Then
Application.Goto Range(Target.Value), True
End If
Exit Sub
Handler:
Dim msg As String
Select Case Err.Number
Case 1004
msg = "Probably invalid address"
Case Else
msg = "Unknown error'"
End Select
MsgBox msg
End Sub
-------- Tag: autosort left to right Tag: 997889
Enquiries about calculation formula
Is there a formula for the the following calculation (a to d)?
I have tried the following way but not successful.
=if(a2>0.01,"1","0",if(a2>100.01,"2","0",if(a2>200.01,"3","0".....continued
to d)
a) If the amount is greater than 0.01, than the rate is $1.00
b) If the amount is greater than 100.01, than the rate is $2.00
c) If the amount is greater than 200.01, than the rate is $3.00
d) If the amount is greater than 300.01, than the rate is $4.50
e) If the amount is greater than 400.01, than the rate is $5.50
Thank you very much for your response. Tag: autosort left to right Tag: 997881
Adding Label Numerical Values correctly
I am trying to ADD the values of 3 label Caption IF they are a Numerical
Value.
I have used the LONG approach below, but it only works on the provision that
the user starts at the label63 then to the Label74 and thirdly to the
Label87.
But if the user starts at label74 or Label87, the below code does not add
the values and place the result into Textbox64.
How can i better code this to allow for ALL not matter what Labels are
numerical, and total the values in TextBox64, without having to add every
possible combinatiopn of the current code i have ?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If IsNumeric(Label63.Caption) = True And IsNumeric(Label74.Caption) = False
And IsNumeric(Label87.Caption) = False Then TextBox64.Value =
Val(Label63.Caption)
If IsNumeric(Label63.Caption) = True And IsNumeric(Label74.Caption) = True
And IsNumeric(Label87.Caption) = False Then TextBox64.Value =
Val((Label63.Caption)) + Val((Label74.Caption))
If IsNumeric(Label63.Caption) = True And IsNumeric(Label74.Caption) = True
And IsNumeric(Label87.Caption) = True Then TextBox64.Value =
Val((Label63.Caption)) + Val((Label74.Caption)) + Val((Label87.Caption))
TextBox64 = Format(TextBox64, "0.00")
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CtM. Tag: autosort left to right Tag: 997872
Subscript out of range error
Hello, I have been trying to fix this error but I have no clue why it's not
working. Any help would be greatly appreciated! I am getting "Subscript out
of range error" when I execute the coding given below, only when I HAVE AN
EXCEL INSTANCE OPEN. If I close all the excel instance and run the code
again, it works just fine. Here is the coding:
------------------------------
Dim xlsApp As Object
'I included this ExcelIsOpen function to see if an excel instance is already
open
'and if so, use that...but that doesn't seem to fix the issue
If ExcelIsOpen Then
Set xlsApp = GetObject(, "Excel.Application")
xlsApp.Visible = True
Else
Set xlsApp = CreateObject("Excel.Application")
xlsApp.Visible = True
End If
xlsApp.Workbooks.Open Filename:="C:\USERNAME\Import_UnmappedMoves.xls",
Password:="password", WriteResPassword:="password"
'am getting the error when the next line executes
Workbooks("Import_unmappedmoves.xls").worksheets("Moves").Activate
------------------------
Do you know where I am going wrong?
Thanks in advance for your help! Tag: autosort left to right Tag: 997868
Frame without background color (invisible)?
I'm not sure if this is even possible but it is -- awsome! =) Is
anyone aware of how to create a frame without any background color?
The reason I ask is I'm trying to lay a frame over a photo / flash
animation object and I can't seem to get it to work correctly. I know
that you can do this effect with textboxes, pictures, etc... but not
sure about frames...
You guys rock! Thanks for helping us out all these times. Tag: autosort left to right Tag: 997867
I hope to get many responses on this.
I am currently in pain over the Sarbanes-Oxley rules and what they are
requiring. I am not sure if I am so upset over the requirements or what
appears to be manangements and Sarbanes auditors lack of knowledge of how to
use excel. The Sarbanes people want to throw more people at every issue and
my feeling is the real issue is their lack of knowledge of excel. Here is a
general idea of my proposal. I feel it would probably take care of half of
what they call deficiencies.
My question is how secure it this: I have to send a pre formatted excel
file "File01" to 10 subsidiaries so they can input thier month end financial
data. The file has 12 sheets. I passworded each sheet and only made
unprotected cells where I want input. I also passworded the workbook. I
also passworded the file. Now to open the file I also send each user a
second file "File02", it's sole purpose is to open the first file with code
which the VBA is also passworded. What I am telling the Sarbanes-Oxley
people is the file is secure and I will know if I get the correct File01
back becuase the first process when I open the file is to see if the file has
a password. If they do not send me the correct file then either the file
will not open because it has the wrong password or they used another file
with no password which I will catch when I ask if the file has a password. I
want to use this method because when I get the 10 files back I can do
basically anything I want with the data using macros to automate the combing
of data anyway that I want. Question: How secure is this method in that the
user will have to use File02 as the opening file to force use of File01 that
has file, workbook and worksheet protection. How easy would it be for
someone to get past all that security? Note: I am thinking if they get past
the file password it will not do them any good because they will not be able
to get past the Workbook and Worksheet security. I will also be testing each
files passwords for Workbook and Worksheet.
Thank you for your comments.
Steven Tag: autosort left to right Tag: 997859
Comparing worksheets with non-identical rows of information
Hi,
I am a newcomer when it comes to functions and programming in Excel which
makes this task all the more difficult. I have a worksheet that is updated
continuously with new/added information and information that is deleted.
This is an inventory worksheet with retail items listed in rows and aspects
of its description located in adjacent cells. Basically, I cannot depend on
an item to be located in the same row each time the worksheet is updated
since things are added and deleted.
What I would like to do is have the original (non-updated) worksheet be
compared to the updated worksheet by:
1. Using the info starting from C3 down (the UPC number for the product) in
the original worksheet and search for this number in the same column (C) in
the updated worksheet.
2. Highlight any differences in columns G, H, I, for the row in question
where the identical UPC numbers are found in both worksheets. (Highlighted in
the original worksheet)
This is a very involved task I would assume, so if an answer cannot be
provided, I would certainly appreciate any suggestions regarding reference
material. Thank you for your time
Glenn Yamada Tag: autosort left to right Tag: 997856
SQL ORDER BY issue
I am creating a SQL SELECT statement in VBA that extracts data from a MS-SQL
DB. All was going well, until I decide to put and "ORDER BY" statement at
the end of the SQL string I was creating. Here is my code snippet:
strTablein = "dbo.hpsc_application"
strFieldin = "Planned_Obs_Date, "
strFieldin = strFieldin & "Actual_Obs_Date, "
strFieldin = strFieldin & "PlannedRetFYQtr, "
strFieldin = strFieldin & "IT_Owner_L2, "
strFieldin = strFieldin & "IT_Owner_L3, "
strFieldin = strFieldin & "IT_Owner_L4, "
strFieldin = strFieldin & "date_of_last_record_update"
strWhere = "IT_Owner_L2 = 'my owner name field (data)'"
strSQL = "SELECT " & strFieldin & " FROM " & strTablein & " WHERE " & _
strWhere & " ORDER BY Actual_Obs_Date DESCENDING"
Debug.Print strSQL
Set rs = con.Execute(strSQL, , 1)
I am getting a sql error on the execute, stating there is an error "near
DESCENDING". I cannot see what that would be. The output of the debug of
strSQL is:
SELECT Planned_Obs_Date, Actual_Obs_Date, PlannedRetFYQtr, IT_Owner_L2,
IT_Owner_L3, IT_Owner_L4, date_of_last_record_update FROM
dbo.hpsc_application WHERE IT_Owner_L2 = 'my owner name field (data)' ORDER
BY Actual_Obs_Date DESCENDING
In the WHERE clause, I am wondering if it is something with the single
quote, or some such. Note, if I remove the "...ORDER BY ....." all the way
to the end of the statement, it works completely fine (just not in the order
I want).
Was hoping another set of eyes might see an obvious error.
Thanks!
PatK Tag: autosort left to right Tag: 997855
Moving Columns Macro
Hi, there.
I am attempting to set a macro to move columns in one spreadsheet to match
the column heading order in a different spreadsheet. If the secondary
spreadsheet always came in the same order, I could just move all of the
columns once and record the macro. However, the secondary spreadsheet column
headings are in no standard order once it is received, but must be in the
same order as the primary spreadsheet when finished. Is there a way I can
set up a macro to search for the column heading and then move it to the
appropriate place to be in the same order as the primary spreadsheet? Tag: autosort left to right Tag: 997849
Data sorting to a new sheet
I'm not even sure this can be done. What am trying to do is sort data from
Sheet 1 and place the sorted data on sheet 2 & 3.
Here is what the data would look like:
1 BLOCKNAME ADDRESS1 STROBE_CIRCUIT_INFO SPEAKER_CIRCUIT_INFO
2 INIT_SD 5.5.001
3 NAC_110 V2-01
A4-03
4 INIT_SD 5.5.021
5 INIT_SD 5.6.001
6 NAC_110 V3-01
A1-23
7 NAC_110 V1-01
A1-01
What I need is all Rows with something in Address1 column goes to sheet1 (in
order) and all Rows with something in column Strobe_circuit_info goes to
sheet 3(in order). I also need them to be grouped on the other sheet (2 & 3).
ex.
3 NAC_110 V1-01
A1-01
30 NAC_110 V2-01
A4-03
57 NAC_110 V3-01
A1-23
so if the if there is a device V1-02 it would go in row 4 and so on, same
for sheet 2
Is this possible or am i really reaching
Thanks for your input Tag: autosort left to right Tag: 997847
Macro not ending?
I've been having a lot of problems and it seems to be that the macro works
perfect the first time; however, when I run it again, the user forms are
shifted around, the combobox value can't be set to show blank, etc. It also
does not do the initialize code in the form after it has been run once. It
does all this after the first time unless I step through the macro and stop
it. Then it seems to reset everything. Can you tell what I am doing wrong?
Please help!
Thanks,
Pam Tag: autosort left to right Tag: 997846
Control button display issue
I am adding buttons with the following code. They are active and
function but do not display. If I set them manually to "Text Only
(Always)" via the customize menu screen, they display fine. I'm using
XL2003.
I am at my wit's end trying to do this programmatically. What am I
missing? Thanks.
.Controls.Add Type:=msoControlButton, ID:=2949, Before:=2
With .Controls(1)
.Visible = True
.Caption = "ReadFile"
.OnAction = "thisworkbook.readfile"
End With Tag: autosort left to right Tag: 997840
AutoIncrementing a Column like a database in Excel 2007
Hi,
We're going to use an SQLite database but we'd like to do all our data
entry in excel. Then we'd like to save the sheets as .csv files and import
the data into an SQLite database. For us to do this successfully we need to
be able to AutoIncrement an id field in Excel like a database would. Does
anyone know the best way to do this?
Thanks,
Keith Tag: autosort left to right Tag: 997835
radio button with VB code
Hello all,
Iâ??m needing the following: 4 radio buttons, â??Defaultâ??, â??GTDâ??, â??ECOâ??, â??BOTHâ??.
When running a VB code, I need to code to look at what button has been
selected and call another VB code or in the case of â??Defaultâ?? it keeps
running the code with out interruption.
So, VB code is running, then it looks at the radio buttons, see that â??GTDâ??
is select and then calls another VB code, then it continues on with the rest
of the code when the other code is finished.
Thanks Tag: autosort left to right Tag: 997827
VBA - Modify data in selection
I need to concatenate a string in a column of a sheet based on user selection.
Column L contains a string to which I need to add either "(SC)" or "(DR#)"
where # is a number between 1 and 7. Ideally, the users can select the rows
they wish to modify (be it 1 row or 1000), right-click the mouse and choose
the appropriate modifier.
In my code, Selekt is a public variable to hold the choice from the
right-click. Note that there are two attempts to make this work with
different coding. The first is from another forum and I think it is closer to
doing what I need. The second is what I tried at the beginning.
Here's the code I have so far:
Private Sub InsertDR()
Dim t As Long
Dim rainj as range
Dim ro as range
Dim rev As String
If Selekt = "0" Then
For i = 1 To Selection.Rows.Count
rev = CStr(Sheet1.Cells(ro, 12).Value)
rev = rev & "(SC)"
Sheet1.Cells(ro, 12).Value = rev
Next
ElseIf CInt(Selekt) < 8 Then
rainj = ActiveSheet.Selection
For Each ro In rainj
rev = CStr(Sheet1.Cells(ro, 12).Value)
rev = rev & "(DR" & Selekt & ")"
Sheet1.Cells(ro, 12).Value = rev
Next
End If
End Sub
Any help here would be hot. Thanks!
--
Adios,
Clay Harryman Tag: autosort left to right Tag: 997826
autosort left to right leaving blanks where they are excel 2007