Yes there is, but it seems to me that it would falsify the records.
On Jan 11, 2:57 pm, AlwaysLearing wrote:
> Hey Guys,
> I have a spreadsheet that has multiple rows of data for each employee
> with each row containing the date they worked and for how long. For
> example, Mr. Smith worked on 7/
Since I am firm believer in writing concise code whenever possible,
especially when assisting others in learning the power of VBA, I
thought it would be helpful to post a shorter version of your
Move_Only_Date_To_Result routine (3 statements instead of 9):
Sub Move_Only_Date_to_Result()
Sheets
Computing
http://soarentcomputing.com/SoarentComputing/ExcelSolutions.htm
On Dec 3, 7:51 am, RolfJ wrote:
> I'd be happy to help, but would need a bit more information.
>
> Rolf Jaeger
> SoarentComputinghttp://soarentcomputing.com/SoarentComputing/ExcelSolutions.htm
>
> On Dec 2,
You could place the following function into a standard VBA module of
your workbook:
Public Function LastUpdated() As Date
LastUpdated = FileDateTime(ActiveWorkbook.FullName)
End Function
And then use it in the cells you would like to show the date of the
last saved update by entering the form
I'd be happy to help, but would need a bit more information.
Rolf Jaeger
SoarentComputing
http://soarentcomputing.com/SoarentComputing/ExcelSolutions.htm
On Dec 2, 4:52 am, bala Subrahmanyam puligadda
wrote:
> Hi ALL,
>
> Please help me in the repeating some calculations by using the macro.
>
>
The syntax for referring to named ranges is Range("{Name of Named
Range}"). Also, I would strong recommend to work with Option Explicit
which will require you to explicitly declare your variables. That
makes your code easier to read and maintain. Give this a try:
Option Explicit
Private Sub Works
Try this:
.Formula = "=SUMPRODUCT((C1:H1= " & UserForm1.ComboBox2.Value & ")*
(A2:A13= " & UserForm1.ComboBox1.Value & ")*(C2:H13))"
Hope this helped,
Rolf Jaeger
SoarentComputing
http://soarentcomputing.com/SoarentComputing/ExcelSolutions.htm
On Dec 2, 9:54 pm, sgltaylor wrote:
> Hi All,
>
> I
This code might do the trick for you:
Const SUMMARY_SHEETNAME As String = "Summary"
Sub HideUnhideSelectedSheets()
Dim rCell As Range
For Each rCell In Range(Worksheets(SUMMARY_SHEETNAME).Range("A2"),
Worksheets(SUMMARY_SHEETNAME).Range("A" & Rows.Count).End(xlUp))
Dim sh As Works
If I correctly understand what your are trying to do this code might
do the trick:
Sub CopyFormulaToRowBeyondLastOfColumnToTheLeft()
Dim targetRange As Range
Set targetRange = Range(ActiveCell.Offset(1), Cells
(ActiveCell.Offset(, -1).End(xlDown).Row, ActiveCell.Column))
ActiveCell.Cop
I can't think of any Excel-intrinsic method that would do this.
However a VBA macro could. If I was to assume that your colored cells
are the ONLY data on active worksheet, give this macro a try:
Sub ExtractNonZeroCells()
Dim db As Range
Set db = ActiveSheet.UsedRange
'First results of
You should provide a bit more detail. What's the formula? Which
characters do you need to change?
On Nov 24, 2:51 am, nikhil deshpande wrote:
> Dear all,
>
> I need to change characters used to show results in (IF ELSE ) formula.
>
> I tried ctrl F- replace. but it is disturbing mu sheet and aski
rivate Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
UserForm1.cmdButton.Caption = "Save Workbook"
UserForm1.Show vbModal
End Sub
Best wishes,
Rolf Jaeger
SoarentComputing
http://soarentcomputing.com/SoarentComputing/ExcelSolutions.htm
On Nov 23, 5:53 pm, RolfJ wro
If I correctly understand what you are trying to do you could add a
UserForm to your workbook, add five checkboxes and one command button
and then place the following code in its VBA module:
Option Explicit
Dim b_UserIsFinished As Boolean
Private Sub CheckBox1_Click()
cmdButton.Enabled = All
Give this alternative a try. It labels all non-empty cells all the way
to the last cell non-empty cell in the column:
Dim aCol As Variant
Dim bCol As Variant
bCol = ActiveSheet.UsedRange.Columns("A")
ReDim aCol(1 To UBound(bCol, 1), 1 To 1)
Dim i As Long
i = 1
Dim irow
'All
> 'my
> 'code
>
> With myMail
> .To = "myn...@myadd.com"
> '.From =
> .Subject = "My files"
> .Body = ActiveSheet.Range("A2").Text & vbCrLf
> .Attachments.Add CurrFile
>
es' list.
Best wishes,
Rolf Jaeger
SoarentComputing
http://soarentcomputing.com/SoarentComputing/ExcelSolutions.htm
On Nov 20, 4:01 pm, RolfJ wrote:
> I am not quite clear exactly when you would like an email to be sent.
> Is it when you change a cell in column A ('Subject Line&
I am not quite clear exactly when you would like an email to be sent.
Is it when you change a cell in column A ('Subject Line') or when you
update an email address in column D. Anyhow here is code that you
could place in the VBA module associated with the worksheet you would
like to respond to chan
This might work for you. Let's assume that the total numbers of
minutes is located in cell A2. The following formulae would calculate
the number of whole days, hours of next fractional day and minutes of
next fractional hour:
Number of days (Cell B2): =INT(A2/60/24) -> 22 for your example
Hours o
Did you already try to simply assign your team's group email address
to the .From property of the mail item?
On Nov 16, 9:01 am, Hanum wrote:
> I have some VBA code that sends an email automatically after attaching
> an Excel file.
> The email gets sent using my corporate email account. Is there
Assuming that your country column is column A, place the following
code into the VBA section of the form containing your three ComboBoxes
(referred to as cmbBoxCountry, cmbBoxState and cmbBoxProduct) and give
it a try:
Private Sub cmbBoxCountry_Change()
Call UpdateComboBox("B", cmbBoxState, c
Please explain why you don't save the worksheet resulting from your
calculations in the intrinsic Excel format (i.e. as an Excel
workbook).
On Nov 8, 12:20 pm, Hemant Hegde wrote:
> Hi
>
> After a lot of coding and lengthy calculations (takes up to 2 minutes) I get
> a sheet (actually a report)
Copy the macro listed below into a standard VBA module in your
workbook and give it a try. The macro will parse the data on the
'Data' worksheet and create another worksheet named 'CleanedUpData'.
You can then setup the PivotTable you want on that worksheet.
Sub ExtractData()
Dim strTransact
Not clear what you mean. Please provide an example.
On Nov 2, 1:00 am, Nagendra Modupalli wrote:
> Hi Team,
>
> Is there any formula to give cells continuous number for filter one.Thanks
> in advance.
>
> --
> Thanks&Regards
> Nagendra.M
>
> "Beautiful photos are developed by negatives in a dark
I am not sure what you mean by fetch, but the easiest way to obtain a
list of just the items that contain "PIPE" or "ELL" would be the
following:
Step 1: Enter the following formula into Cell E4
=IF(ISNUMBER(SEARCH("PIPE",A4)),"PIPE",IF(ISNUMBER(SEARCH
("ELL",A4)),"ELL",""))
Step 2: Copy the fo
Place this code in the VBA module for the worksheet you would like to
restrict and give it a try (needless to say: for this password
strategy to be bullet-proof you would have to password-protect the VBA
project, so that none uf your users can have access to the passwords):
Const RANGE1_PWD = "ab
John Walkenbach's "Excel VBA Programming for Dummies' (this is not
intended as an insult. it's REALLY a great book to get started):
http://spreadsheetpage.com/
Hope this helped,
Rolf
On Oct 29, 10:42 pm, khalid khan wrote:
> Dear friends
>
> Please suggest me book for Excel and VBA coding
>
> t
Place this code into a standard VBA module of your workbook XYZ.xls
and give it a try (you will still need to add a button that invokes
the macro, and I would suggest to let the user decide where to save it
by using the standard File | Save As command) :
Sub ConsolidateFilesIntoMasterWorkbook()
In principle that should be possible, but without knowing how the main
worksheet is structured it is difficult to make any specific
suggestions. It would help if you were to post a sample workbook or
described the structure in some other way.
On Oct 30, 7:13 am, Leo wrote:
> Hello! noob here :)
Let's assume that your 3 macros are located in a standard VBA module
and look like this:
Sub Macro1()
MsgBox ("Macro1")
End Sub
Sub Macro2()
MsgBox ("Macro2")
End Sub
Sub Macro3()
MsgBox ("Macro3")
End Sub
Now insert a UserForm in your VBA project then drop a ListBox (named
ListBox1
I was able to reproduce your problem and can offer a solution, however
regrettably without being able to explain why it works. Replace the
section of your code that deals with the unprotecting, copying and
pasting, and re-protecting:
Worksheets("Computation").Range("compute").Copy
ay you be
> blessed with good health and long life.
>
>
>
> On Wed, Oct 28, 2009 at 11:20 PM, RolfJ wrote:
>
> > Please find below a revised and annotated version of your Post_to_Dbase
> > () routine. I corrected what seemed to me a couple of logic errors
> > (wh
uot;Payroll closed and posted, you may print payslips now!"
> Else
> MsgBox "Payroll Period is already posted!"
> End If
> End With
> With Application
> .ScreenUpdating = True
> .DisplayAlerts = True
> E
SearchDirection:=xlNext, _
> MatchCase:=False)
> If Not Rng Is Nothing Then
> 'paste the entire ranged named to dbase worksheet
> ActiveSheet.Calculate
> Worksheets("Computation").Range("compute&
ll range (example A1 and C6) to color
> 3. Based on the above cell ranges A1:C6, the macro should color(Any
> color) the cells.
>
> Hope you got my question.
>
> Thanks
>
> On Oct 27, 2:03 am, RolfJ wrote:
>
>
>
> > I am sorry, but I don't understand what you
Sheets("PAYSLIP").Select
> Range("D41").Select
> Selection.PasteSpecial Paste:=xlPasteValues
> Application.CutCopyMode = False
> 'Print_All_Pay
> Range("B1:N68").Select
> Selection.PrintOut Copies:=1
>
ser form with 2 input fields
> asking for cell range (example A1 and C6) to color
> 3. Based on the above cell ranges A1:C6, the macro should color(Any
> color) the cells.
>
> Hope you got my question.
>
> Thanks
>
> On Oct 27, 2:03 am, RolfJ wrote:
>
>
>
> &
Place this code into the VBA module associated with worksheet Sheet1
of your workbook and give it a try:
Private Sub GO_Click()
TransferClaimData
ClearForm
End Sub
Sub TransferClaimData()
Dim r As Range
Set r = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset
(1)
If
> Regards,
> Sri
>
> On Oct 24, 4:53 am, RolfJ wrote:
>
>
>
> > I can't figure a way to do this by way of conditional formating, but I
> > can offer some VBA code that would do the trick. Place the following
> > code into the a standard VBA module of you
VALIDATE THE SAME.
>
> THANKS FOR YOUR REPLY.
>
> RAHUL GARG
>
> --- On Fri, 23/10/09, RolfJ wrote:
>
> From: RolfJ
> Subject: $$Excel-Macros$$ Re: TO PASTE THE DATE IN ATTACHED FORMAT & VALIDATE
> IT
> To: "MS EXCEL AND VBA MACROS"
&g
It would help to see an example of what you are trying to accomplish.
On Oct 26, 4:51 am, Anantha wrote:
> Hello Folks,
>
> For one of my assignments I'm trying to write a formula that searches
> a list rom a search space.
>
> Here the list is a set of cells might have same or different values,
I am having difficulties following what you would like to do. In
particular it is not clear to me what you mean when you say 'it
stops'. Also, what to you mean to indicate with the - debugs here
flag?
Please post or e-mail example worksheets and I'll take a closer look.
On Oct 26, 6:28 pm, O
Microsoft requires the parameter of the MInverse method of data type
Variant (http://msdn.microsoft.com/en-us/library/bb239421.aspx). Try
declaring CoArrInv as Variant (as in Dim CoArrInv(3,3) As Variant).
Hope this helped,
Rolf
On Oct 18, 10:36 pm, seryozha wrote:
> Hello all,
>
> I am atte
If I correctly understand your requirement this code should do:
Sub InsertRowsAfter8DigitRow()
Dim r As Range
Set r = ActiveSheet.UsedRange.Columns("B")
Dim rCell As Range
For Each rCell In r.Cells
If rCell.Row >= 3 Then
If rCell.Offset(-1).Value = "" And Is8Di
I can't figure a way to do this by way of conditional formating, but I
can offer some VBA code that would do the trick. Place the following
code into the a standard VBA module of your workbook:
Public Sub ColorTable()
Dim rRow As Range
For Each rRow In ActiveSheet.UsedRange.Rows
I
I think I can help you with item 1, but don't understand item 2.
Here's my suggestion for item 1. Let's assume you have a macro named
macro1 then the code for what you are trying to do would be:
Activate Workbook
macro1
End Sub
There were two problems with your code:
1) Spaces are not allowe
Following Hernant's suggestion I would consider to encapsulate this
process in a totally stand-alone C# application which instantiates a
separate Excel session that wouldn't interfere with your regular Excel
session. I would have to see the current VBA code to see how easily
this could be created.
Hi Rahul:
you will need to provide a LOT more information than this if you want
anyone to assist you with this problem. The first thing I noticed when
I opened your workbook was that you password protected your macros.
That's not a great start, because it prevented me from trying to
understand wh
If you are REALLY concerned about the users of your workbook using
specific commands you could also turn them off by using the OnKey
method in the Workbook_Open event handler. Here's the code on how to
do that for "CTRL+X":
Private Sub Workbook_Open()
Application.OnKey "^{x}", ""
End Sub
Che
As long as you DON'T try to do any calculations with cell B5 (for the
reason explained by Paul) you would use the following formula in cell
B5:
=IF(B1="USD K",CONCATENATE("USD ",TEXT(B11,"#,###")),CONCATENATE("INR
",TEXT(B10,"#.#")))
If you do need to do calculations with the value in cell B5 I
If you don't have Excel2007 it gets a bit more complicated. Here is
one way to accomplish what you would like to do (for the sake of
simplicity I am assuming that your dates are in column A, and that
column B can be used as helper column and that you have a header in
the first row (cell A1)):
Ste
If I correctly understand Nader's requirement he would like to
hyperlink each cell in one sheet to the corresponding cell in a second
sheet (not all cells to cell A1 of the second sheet). I am not aware
of a simple copy and paste route that would accomplish that, but can
offer the following macro
The simplest solution to this is to define names for your target
cells.
This is how you do that, e.g. for 'Ann': highlight the cell Ann in
your Master sheet and then select Insert | Name | Define; the name for
this cell automatically defaults to 'Ann' which is probably what you
want; now go to th
This pointer should help: http://www.ozgrid.com/Excel/run-macro-on-time.htm
On Oct 11, 1:59 am, Manish Pansari wrote:
> Hello Friends,
>
> I want to run a macro continuously after every five minutes. Is there
> any option, by using that I can set macro run time and can run macro
> automatically?
Once you include add a reference to the Microsoft Outlook library to
your VBA project (using Tools | References in VBE), you can send
messages with code like this:
Sub SendMail()
Dim objOut As Outlook.Application
Dim objMail As Outlook.MailItem
Dim strTo As String
Dim strCC As Str
I am not clear on what you want to write code for. Do you want to
eliminate the space or do you want to intercept Excel's response to
the (invalid) space in the e-mail address?
On Oct 11, 3:44 am, karthikeyan
wrote:
> Hi,
> I have enter E-mail id in one cell with space:e.g(karthi keyan
> sankar.
It appears that you cross-posted this also at
http://www.excelforum.com/excel-programming/702626-excel-macro-to-close-powerpoint.html
For the benefit of the members of this forum, here the solution I sent
you:
Sub Workbook_Open()
Dim PPApp As PowerPoint.Application
Set PPApp = GetObject
There are of course keyboard/menu equivalents for both (but I am
probably telling you the obvious, hence maybe am missing the point of
your question):
- Filling: Edit | Fill | Series (choose either rows or columns, then
enter stop value and click on OK
- Copying of a Formula:
- Step 1: copy th
You are describing exactly what I had proposed. Should be
straightforward to implement.
Have fun,
Rolf
On Oct 8, 7:45 am, chinmay kamat wrote:
> Thank you sir, but unfortunately this does not solve the problem.
>
> The data is in the following format (example):
>
> For purchases:
> Date
Are you sure the ExitApp macro you placed in your workbook Rpt1.xls
actually works without error? It seems to me that the UserForm UF1 in
that context...
Anyhow I think you can accomplish what you are trying to do simply by
placing the following event handler into the ThisWorkbook VBA module
of y
You could use the DATEDIF function (see e.g.
http://www.cpearson.com/excel/datedif.aspx).
Let's assume that your purchase date is in cell A2 and you sell date
in cell B2. The formula =DATEDIF(A2,B2,"d") returns the number of days
between the two dates.
One way to use this function for your scena
It seems that the code you posted is dealing with only one of the two
year columns you mentioned in your post. Give this code a try (after
modifying the constant COL2 to match the range for the second year
column) and see whether it accomplishes what you would like to see
happening:
Const COL1 As
Assuming that you already populated the listbox (named ListBox1) with
the file path names of Excel workbooks you could place this ListBox
click event handler in the VBA module of the UserForm containing your
listbox:
Private Sub ListBox1_Click()
If Dir(ListBox1.Value) <> "" Then Workbooks.Ope
Assuming that you already populated the listbox (named ListBox1) with
the file path names you would need to add this ListBox click event
handler to the VBA module of the UserForm containing the listbox:
Private Sub ListBox1_Click()
If Dir(ListBox1.Value) <> "" Then Workbooks.Open (ListBox1.Va
> declaration in current scope." for statement --> Dim path as string
>
> Please help.
>
> Suyog
>
> On Oct 2, 1:49 am, RolfJ wrote:
>
>
>
> > Got it. Try declaring fso this way:
>
> > Dim fso As Object
> > Set fso = CreateObject(&q
I have two comments.
1) If I correctly understand what you are trying to accomplish, you
could simply put the formula COUNTIF(AR2:CE2,1) in cell K2 and copy it
down to K3:K250.
2) It seems to me that the problem with your code is that your COUNIF
formula references the whole range (arange) not s
r daily reports, and only your
> daily reports (!), are located in the folder "C:\test". Is that
> correct?
> Ans: Yes
>
> 5. The code also assumes that the format of your weekly report is
> identical to the format of the daily reports. Is that the case?
> Ans: Yes
&
Alternatively, but still along the lines proposed by Paul Schreiner,
you could make your code more concise by using this approach which
avoids all the activating and selecting that will slow your code down
once you apply your code to larger ranges:
Dim searchRange As Range
Set searchRange
It seems to me that you skipped over the setting of the database
itself, i.e. the Set dbs = statement is missing. Therefore your VBA
code doesn't know what dbs is.
On Oct 1, 12:05 am, kuhrty wrote:
> I am trying to create a table in Access 2003 through VBA. I keep
> getting an error 91 where th
You need to be a little bit more specific in HOW this code is not
working for you. Obviously it is critical that you specify the range
of cells you want to copy (in the code you showed it is set to
"B28:F28". Is that correct? Also the code assumed that your daily
reports contain the date you want
69 matches
Mail list logo
