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: Formula bar is hidden, how do I unhide it? 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: Formula bar is hidden, how do I unhide it? 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: Formula bar is hidden, how do I unhide it? 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: Formula bar is hidden, how do I unhide it? 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: Formula bar is hidden, how do I unhide it? 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: Formula bar is hidden, how do I unhide it? 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: Formula bar is hidden, how do I unhide it? 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: Formula bar is hidden, how do I unhide it? 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: Formula bar is hidden, how do I unhide it? 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: Formula bar is hidden, how do I unhide it? 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: Formula bar is hidden, how do I unhide it? 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: Formula bar is hidden, how do I unhide it? 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: Formula bar is hidden, how do I unhide it? 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: Formula bar is hidden, how do I unhide it? 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: Formula bar is hidden, how do I unhide it? 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: Formula bar is hidden, how do I unhide it? 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: Formula bar is hidden, how do I unhide it? 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: Formula bar is hidden, how do I unhide it? 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: Formula bar is hidden, how do I unhide it? 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: Formula bar is hidden, how do I unhide it? 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: Formula bar is hidden, how do I unhide it? 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: Formula bar is hidden, how do I unhide it? 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: Formula bar is hidden, how do I unhide it? 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: Formula bar is hidden, how do I unhide it? 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: Formula bar is hidden, how do I unhide it? 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: Formula bar is hidden, how do I unhide it? 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: Formula bar is hidden, how do I unhide it? 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: Formula bar is hidden, how do I unhide it? 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: Formula bar is hidden, how do I unhide it? 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: Formula bar is hidden, how do I unhide it? 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: Formula bar is hidden, how do I unhide it? 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: Formula bar is hidden, how do I unhide it? 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: Formula bar is hidden, how do I unhide it? Tag: 997826
excel macro to open Sharepoint list in excel and save in my folde
Hi
I have created a share point list. and i want to save as excel file in my
folder
I need help to write macro to open Sharepoint list in excel and save in my
folder.
so when i click on button (macro) it should do this process.
Thanks! Tag: Formula bar is hidden, how do I unhide it? Tag: 997821
Creating a working program / application from an Excel sheet
I have a complex spreadsheet i am looking to market, however i dont want to
use Excel as the platform.
How would i go about creating an actual application from the spreadsheet
that can stand alone without the need for Excel?.
Is there a way to easily create an application from a complex sheet?
Can anyone help?
Thanks,
Roger Tag: Formula bar is hidden, how do I unhide it? Tag: 997817
macro to compare few columns data of two spreadsheet
Hi !
I want to compare Few columns of two spreadsheet using macro.
Can anyone help me writing code
Thanks! Tag: Formula bar is hidden, how do I unhide it? Tag: 997812
Find End of Range,
I am hoping to find a way of finding the end of a used range (different on
each sheet), move over five columns, and color the entire range, in column X,
from cell X6 to this cell lower boundary of the used range.
I can find the end of the used range with this line of code:
Cells(Rows.Count, "S").End(xlUp).Select
I need to do this for several spreadsheets in a workbooks that I just
created; I am using a For...Next Loop and this part is fine...just need to
get a handle on this colored range thing.
--
RyGuy Tag: Formula bar is hidden, how do I unhide it? Tag: 997806
Help to code Macro to Copy fron one sheet and paste in other sheet
Hi
I have tow excel sheets.- sheet1 and sheet2
in sheet2 i have all columns from sheet1 + additional columns.
in sheet1 i have unique Action# column which does not have dublicate data.
and that ACtion# is in sheet2 (as i have copied Sheet1 data).
So what i want when some one adds or update data of sheet1,
i want to run a macro which adds new data(rows) into sheet2 and updates data
as well (update means- if someone updates the data in sheet1, it should
update those data in sheet2 when you run a macro)
So need help to code macro which adds data in sheet2 and if data is there
then update it.
I appreciate for any help.
Thanks! Tag: Formula bar is hidden, how do I unhide it? Tag: 997801
count formula problem
I have a formula in excell to count the amount of cells that contain numbers.
I use this formula =COUNT(A1:A90) and if the count was 30 I would get 31. I
have two other sheets in the same file where the calculations are correct.
The only way I can get the correct calculation is to do this formula
=COUNT(A1:A90)-1
Any ideas? Tag: Formula bar is hidden, how do I unhide it? Tag: 997798
Transparent .XLA?
Take this in the context of a total noob's groping around.
Looks to me like .XLA's (or, at least, their use) are
user/PC-specific and not application/spreadsheet-specific.
i.e. If user Smith is able to use it on his desktop PC, he may
not be able to use it with the same spreadsheet on his laptop.
i.e. For a spreadsheet to be able to use code in Whatever.xla,
each user's MS Office installation has tb configured to point to
it as an Add-In.
If I'm correct so far, I'm looking for a way to deploy 70+
identical .XLS files to 70+ different users but have all 70+
.XLS' use the same code module - so if we need to change the code
we're not up a creek.
We can live with the .XLA living on a LAN drive (i.e. a user
working offline cannot get to it).
Any solution that requires anybody to do anything on the user's
PC is out of the question. It has tb 100% transparent.
The first thing that comes to my mind is to have each .XLS'
Workbook_Open event somehow check to see if the user's PC/MS
Office has an Add-In pointing to our .XLA and, if not, create one
in such a way that it points to the common version on a LAN
server without copying it to C:\Program Files\WhereverAddinsLive
The second thing that comes to mind is doing the same check to
see if there's an Add-In and creating one if not - but pointing
it to the user's C: drive and unconditionally copying the .XLA to
somewhere on the user's C: drive if the LAN is available, and
just quietly not doing so if the LAN is not available. Seems
like this would give a little more portability, yet still let us
push changes down to each user.
Are either of these "good practice"?
If not, can somebody suggest something?
--
PeteCresswell Tag: Formula bar is hidden, how do I unhide it? Tag: 997794
Cycle through all worksheets in a workbook
I have some code which automatically copies and pastes data off all workbooks
(1 sheet each) in a folder.
How about if I want to make it cycle through multiple worksheets within a
workbook before closing it?
I made a few modifications to this loop, but it is not working. Anyone know
why?
Do While sFil <> ""
Workbooks.Open sPath & sFil
For Each w In ActiveWorkbook.Worksheets
<---added this
With ThisWorkbook.Worksheets("Bulk")
<---and this
k = ThisWorkbook.Sheets(1).Range("A65536").End(xlUp).Row
n = k + 4
Set oWbk = Workbooks.Open(sPath & "\" & sFil)
Range("B4:Z1000").Copy
Set w = ThisWorkbook.Sheets(1)
ThisWorkbook.Sheets(1).Range("A" & n).PasteSpecial
End With
<---added this
Next w
<---and this
oWbk.Close True <--should this be False
I appreciate your help as always.
Arlen Tag: Formula bar is hidden, how do I unhide it? Tag: 997790
time box
I want to make a box which is in military time tied back to a range of cells.
Can it be done and how could it be easily done. Thanks for help
--
ED Tag: Formula bar is hidden, how do I unhide it? Tag: 997789
Search for cell color, copy values in other columns
Hi everyone,
I am trying to put together a tool that can can search, column by column,
for a certain background/fill color (yellow), and then copy/paste the
value/text in various columns of the same row for each instance of yellow
background colored cells, into another worksheet.
I think I know how the first part of this problem can be solved (searching
for and identifying the background color cells), but how can I code to
copy/paste the values in specific cells? Tag: Formula bar is hidden, how do I unhide it? Tag: 997788
Top Property in Excel 2007 Not Working Properly
I previously made a step up chart with beginning, middle, and end totals on a
bar chart. These totals were scattered bar charts with the data labels
positioned at the top. In between the total bars were a series of stacked
bars with the lower bars whited out to make it look like the bars were being
stepped up or down to get to the totals. The data labels for the step bars
would be progamatically positioned above or below the step bar whether it was
positive or negative by using the top property. Well I currently switched
over to Excel 2007 and the top property doesnt work anymore and I can't
figure out how to position the data labels how they would previously be
positioned. The values in the step up chart change based on configuration. Tag: Formula bar is hidden, how do I unhide it? Tag: 997784
VB Generated Hyperlinks
My need is this; I have a master spreadsheet with Column A being a list of
9 character control numbers which are text entries for hyperlinks to another
folder filled with Excel spreadsheets, whose first nine characters are the
control numbers matched in the master Spreadsheet. What I would like is an
update spreadsheet button; not a new toolbar button, on the master
spreadsheet that will tell excel to go look in a specified folder, it will
always be the same folder, check to see if there are any files in the folder
which are not on the master spreadsheet already. If there are new files
create a new hyperlink; titled with the first nine characters of the file
name in the next available cell in Coloumn A. When it is done updating a
little window saying how many entries were created or if none were created
would be nice, but not neccessary.
I am not a VB programmer by anymeans and my Excel formula skills are
moderate, so any help with this would be greatly appreciated, thank you. Tag: Formula bar is hidden, how do I unhide it? Tag: 997783
xla export classes, modules and forms
In a regular excel document I can export all of the code by the following:
Sub ExportAllVBA()
Dim VBComp As VBIDE.VBComponent
Dim Sfx As String
For Each VBComp In ActiveWorkbook.VBProject.VBComponents
Select Case VBComp.Type
Case vbext_ct_ClassModule, vbext_ct_Document
Sfx = ".cls"
Case vbext_ct_MSForm
Sfx = ".frm"
Case vbext_ct_StdModule
Sfx = ".bas"
Case Else
Sfx = ""
End Select
If Sfx <> "" Then
VBComp.Export _
Filename:=ActiveWorkbook.Path & "\" & VBComp.Name & Sfx
End If
Next VBComp
End Sub
If I go inside of my xla and run this,
I can only get the thisworkbook.
none of the forms, classes or modules get exported.
is there a way to export these in an xla? Tag: Formula bar is hidden, how do I unhide it? Tag: 997780
Need Alternate Code or Formula
Hi friends,
I have the formula below, it is in column BR. It goes from column V to BN
and it should be copied down from row3 to Row 700. The problem I am having is
the formula is too long for the cell, what would be the equivalent VBA code
or is there a different formula that will work for this?
Thanks for any assistance you can provide.
=IF(V3="X",V$1,â??â?? )&â?? â??&iF(W3="x",W$1,â??â??)&â?? â??&IF(X3="x",X$1 ,â??â??)&â??
â??&IF(Y3="x",Y$1 ,â??â??)&â?? â??&IF(Z3="x",Z$1&â?? â??&â?? â??,â??â??)&â?? â??&IF(AA3="x",AA$1 ,â??â??)&â??
â??&IF(AB3="x",AB$1 ,â??â??)&â?? â??&IF(AC3="x",AC$1 ,â??â??)&â?? â??&IF(AD3="x",AD$1 ,â??â??)&â??
â??&IF(AE3="x",AE$1 ,â??â??)&â?? â??&IF(AF3="x",AF$1 ,â??â??)&â?? â??&IF(AG3="x",AG$1 ,â??â??)&â??
â??&IF(AH3="x",AH$1 ,â??â??)&â?? â??&IF(AI3="x",AI$1 ,â??â??)&â?? â??&IF(AJ3="x",AJ$1 ,â??â??)
The sheet looks like this:
v w BR
1Criteria1 Criteria 2
2 x =if(V2="x",V$1,"")&" "&if(W2="x",W$1,"")
3 x x =if(V3="x",V$1,"")&" "&if(W3="x",W$1,"")
4
--
Danielle :<) Tag: Formula bar is hidden, how do I unhide it? Tag: 997779
IF a condition is met
I have a workbook comprised of several worksheets. I need to print any row
with a word "high" in any worksheet to a new worksheet of all records
containing word "high" only. Please help! Tag: Formula bar is hidden, how do I unhide it? Tag: 997771
Somehow I have hidden the forumla bar and cannot make it visible again.