something specific, I can VERY QUICKLY
determine if the record exists and return its locations (array indexes)
from the dictionary.
much, much, MUCH faster than looping through an array!
Paul
-
“Do all the good you can,
By all the means you can,
In all the ways
Your file utilizes a "Data Dictionary" object to collect unique records and
concatenate all values from column "B" for each unique value in column "A".
the attached file adds some additional comments to the code.
Paul
-
“D
:
Visual Basic for Applications
Microsoft Excel 14.0 Object Library
OLE Automation
Microsoft Office 14.0 Object Library
Microsoft Word 14.0 Object Library
Microsoft Forms 2.0 Object LIbrary
Paul
-
“Do all the good you can,
By all the means you can,
In all th
(ActiveSheet.Cells(R, C).Value <> "") Then
If (Len(AddrList) > 0) Then AddrList = AddrList & ";"
AddrList = AddrList & ActiveSheet.Cells(R, C).Value
End If
Next C
Then, pass Addrlist to your EmailDta() function.
Paul
-
this by recording a macro, in which I changed the cell format to
"Text")
also:
in your macro, when you're looking for a blank row, there's LOTS of ways to do
this, of course, but your loop should look more like:
For NewRow = 5 To 1000
If Worksheets("BankDa
like.
Paul
-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John W
t you step
through.
Like:
Private Sub Workbook_Open()
Debug.Assert False
End Sub
Paul
-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
idea what you're looking at on your screen
and describe what it is you have, and what you want to accomplish.
Paul
-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all
Is this related the "Copy & Paste only particular to another sheet"?
or is it a new question?
Paul
-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all
r q=5 to 1000 statement will then increment q to 7!
So, your loop is only checking q=5,7,9,11, etc.
if you want it to check all rows, remove the line: q = q + 1
Paul
-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the
I have seen it ; look into code after going back home
On Feb 2, 2015 3:58 PM, "Vaibhav Joshi" wrote:
> Great, pl note i have replaced your code..
>
> On Mon, Feb 2, 2015 at 3:53 PM, Soumyendu Paul
> wrote:
>
>> Thanks Vaibhav it's working
>> On Fe
Thanks Vaibhav it's working
On Feb 2, 2015 12:23 PM, "Vaibhav Joshi" wrote:
> Hi Soumyendu..
>
> check this..
>
> Cheers!!
>
> On Mon, Feb 2, 2015 at 10:58 AM, Soumyendu Paul
> wrote:
>
>> Please help!!
>> On Feb 2, 2015 7:21 AM, "Soumy
Please help!!
On Feb 2, 2015 7:21 AM, "Soumyendu Paul" wrote:
> I have already shared file please see attachment
> On Feb 2, 2015 3:27 AM, "Paul Schreiner" wrote:
>
>> Can you share the file?
>> How are you using/displaying a timer?
>>
>> yo
I have already shared file please see attachment
On Feb 2, 2015 3:27 AM, "Paul Schreiner" wrote:
> Can you share the file?
> How are you using/displaying a timer?
>
> you said you're using a selectionchange event, but what type of timer
Can you share the file?
How are you using/displaying a timer?
you said you're using a selectionchange event, but what type of timer are you
using?
Paul
-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the place
I have developed a timer using selection change event. But problem is how
to stop timer using command button while timer is runng.
please help!
File attached
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook pag
o, do you NOT include the time from 5:30pm to midnight?
What does the time represent?
I think I'd need to know what the INTENT is in order to recommend a solution.
Paul
-
“Do all the good you can,
By all the means you can,
In all the ways you can,
nge and see if it changes when I switch to a
different worksheet.
Paul
-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as eve
what version of excel are you using and what file format?
If you're using Excel 2007, 2010, 2013, you should save it in binary .xlsb
format.
My files sizes (compared to .xls and .xlsx,.xlsm formats) are 30%!!!
Paul
-
“Do all the good you can,
B
, with userforms, when an error occurs, it often simply exits the
entire macro and does not allow a debug option!
Paul
-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all th
m.
Use the SelectionChange event to determine if the specific cell is selected and
open the userform.
The userform change event IS on a keystroke basis.
you update the autofilters as keys are typed, then write the value to the cell
when the userform exits.
Paul
ou to
convert Excel 2007 files to Excel 2003:
http://www.microsoft.com/en-us/download/details.aspx?id=3
Paul
-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the p
two lines:
ActiveSheet.protect DrawingObjects:=True, Contents:=True, Scenarios:=True,
Password:="deck"
or:
ActiveSheet.protect _
DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True, _
Password:="deck"
Paul
ing the keyword "Public" as in:
Public Sub WEBADDcheck_up()
would actually make the macro available to other workbooks...
hope this helps.
Paul
-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the place
heet to add
a new change Record.
then it uses .Offset(0,1).Value and others to assign values to different
columns.
(it actually increments the columns if you fill up a sheet of changes)
If you want to record the value of column "H" in the cell that is 12 cells to
th
step into" the macro one line at a time.
by right-clicking a variable and selecting "add watch", you can continuously
minitor the value of variables, and even change the values for testing purposes!
give it a try and see how it works.
Paul
-
“Do
otals with values.
So, in this column, the subtotal recognizes them as simple values and includes
them accordingly.
Paul
-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To a
Even the, "B2:B9" doesn't define a RANGE, it defines a STRING.
you need to tell VBA to interpret the string as a range.
MsgBox WorksheetFunction.Subtotal(9, Range("B2:B9"))
should work.
Paul
-
“Do all the good you can,
By all the mea
eSheet.UsedRange.Select
For i = 48 To 57
Selection.Replace _
What:=Chr(i), _
Replacement:=Chr(i), _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False
You probably ought to post this on a forum for Word instead of Excel.
The differences are significant and it sounds like what you need requires
assistance from someone with more than just casual knowledge of Microsoft Word.
Paul
-
“Do all the good you can
I'm sorry, but according to the forum rules:
3) Don't post questions regarding breaking or bypassing any security measure.
we should not address that here.
If you use Google, you'll find thousands of methods and service providers!
Paul
--
Do you have any experience in Excel VBA?
It would be fairly simple to write a macro that lists the files in a folder.
then, create a column that has the new name and have another macro rename the
file.
but implementing it requires that you know a little about VBA.
Paul
and import them into the new workbook, and whatever corruption isn't carried
along.
A file my boss updates periodically has the same issue once a year or so.
I elect to recover it, then overwrite the original and it works until the next
time.
Paul
-
You could use Data Validation to create a selection list.
See attached.
Paul
-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you
at are between two dates?
if THAT's the case, then you can possibly use Conditional Formatting.
otherwise, can you send me a sample sheet showing what you have and what you
want it to do?
Paul
-
“Do all the good you can,
By all the means you can,
d show you whatever value is in cell
A3.
because it's "indirectly" interpreting the value in B1 as a cell reference.
-
Let me know what your worksheet looks like and how you expect these dates to be
"returned" and I'll see what I can do to help.
Paul
---
.
If the selection is changed, the cell contents is updated.
thanks for your consideration.
Paul
On Friday, December 5, 2014 1:36:42 PM UTC-5, Paul Schreiner wrote:
> I have a worksheet template in which I display a 3-digit Product Code.
> I have a hidden worksheet that has a list of P
() function.
The most basic version is:
DateDiff("d",Start_Date, End_Date)
or:
MsgBox DateDiff("d", "12/12/2014", "12/22/2014")
hope this helps,
Paul
On Friday, December 12, 2014 7:18:05 AM UTC-5, Mandeep Baluja wrote:
> Just for knowledge can we get
elect the PDF file type!
(The option is even avaliable in VBA)
Paul
-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.”
s(oRow, "D").Value = ws.Cells(Data.Row, "Y").Value
End If
Next Data
'--
tstop = Timer
tElapsed = tstop - tstart
tMin = tElapsed \ 60
tSec = tElapsed Mod 60
MsgBox "Finished&
range, then change the value.
the same thing can be done with:
Sheets("Sheet1").Range("A1").Value = "Test"
In fact, you can even read/write to sheets that are HIDDEN!
Which, of course you cannot do if you have to select/activate the sheet.
Paul
---
which one precisely contains the StrComp function.
I'd suggest testing it and see if it works.
Paul
-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people y
ing for and we'll help you find it.
In doing so, perhaps you'll learn how to find the rest.
Then of course, there's books.
Haven't used them myself since my first class in Fortran-IV in the 80's.
So I'm probably not a good one to ask about books... :)
Paul
--
rkill.
Paul
-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
-
From: Ma
Field that would allow the
user to select from the code/product name list
but once the selection is made, only the 3-digit code is displayed in the
box.
I THINK I've done something similar in the past, but cannot recreate it.
what am I missing?
thanks,
Paul
--
Are you =EXP(E:RT) or =NO
_Write group members have the same sheets visible, but fields are
unlocked.
Admin groups see all sheets and all are unlocked.
This technique doesn't rely on people remembering their password, but instead
relies on their ability to log into Windows and the N
acro one line at a time.
right clicking on a variable (like k or cell or dicobj)
and selecting "Add Watch" will allow you to put this variable into a "watch
window"
and thereby monitor it's value while stepping through the code.
Paul
---
ot;*.xlsx")
Note: the use of the term "collection" in this case is NOT the same as a true
Excel Collection object.
Paul
-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places yo
xDic.Add xChar, 1
End If
Next I
xOutValue = ""
sArray = xDic.keys
For I = 0 To UBound(sArray)
If (xDic.Item(sArray(I)) = 1) Then
xOutValue = xOutValue & sArray(I)
End If
Next I
RemoveDupes1 = xOutValue
End Function
You're not going to be able to do that.
Can I ask why?
I cannot imagine how you could create a variable that you don't know the name
for,
then use it later in a macro.
If you can provide some context and explain what you're trying to accomplish,
perhaps I c
e"
arname(2, 0) = "city"
End Sub
your loop:
For i= 0 T0 2
arname(i) (50) as string
Next i
would not work, even if you used ReDim properly,
since it makes the assumption that each first-level element of the
array (i = 0 to 2)
could have a diffenent secondary element
(which i
Could you double-check your calculation again?
in your sample, transction #6 (12:30-14:30) is completely contained within
transaction #5 (12:00-16:00)
so would not contribute to the total.
my calculation comes up with 5:50 instead of 6:20
Paul
-
“Do all
Can you send me a sample file?
Otherwise, I'll probably spend far too much time trying to recreate something
that doesn't apply to your situation.
Paul
-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the
.
If you can send me a file with some "dummy" data (even sent directly to my
email)
I'll see what I can do.
Do you have any experience with Excel macros?
I'm just concerned about how you're going to implement any solution...
Paul
---
ActiveWorkbook.Sheets(1).Range("A1:A65000"))
'If (nRows > 104) Then nRows = 104
tRows = ""
Range("A1").Select
For R = 1 To nRows Step 2
If (Len(tRows) > 0) Then tRows = tRows & ","
tRows = tRows & R
Next
).Cells(sCnt, "A").Value =
Sheets(sht).Cells(R, "A").Value
Sheets("Summary").Cells(sCnt, "B").Value =
Sheets(sht).Cells(R, "B").Value
Sheets("Summary").Cells(sCnt, &quo
ThisWorkbook.Sheets(1).Range("A2:A65000").ClearContents
tArray = Dict_Unique.keys
For R = 0 To UBound(tArray)
ThisWorkbook.Sheets(1).Cells(R + 2, "A").Value = tArray(R)
Next R
End Sub
Paul
-
“
BadExit:
MsgBox Err.Number & Chr(13) & Err.Description
Debug.Assert False
End Sub
Paul
-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people
Do you mean like:
=IF(AND(C5>1.1,C8>1.1),0.8,C10/C4)
Paul
-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John
e:
=A2/100
(see attached)
Paul
-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - J
What version are you USING?
(are you using 2007 and opening a 2003 file?)
Paul
-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever
t limit it to the specific rows.
At least I think it will work.
I've only used 2010 to look up in 2003.
Not 2003 to 2010.
it might be that 2003 cannot do it at all!
Paul
-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In
Then just change the cell format to percent.
The VALUE remains the same.
Cell formatting simply changes the way it is DISPLAYED.
Keep in mind that a value of 1 is 100%
90% is .9
So, if your VALUE is something like 65,
that's not a percent.
(unless you were expecting 6500%)
ng specific so
that I can step throught the code to see what's going on.
In a case like this, using
Debug.Assert False
is like a "conditional breakpoint"
It PAUSES the macro, just like a breakpoint.
Debug.Assert True passes through and does nothing.
Wha
ebug.Assert BreakFlag
whereever I need a breakpoint.
Then, when record # or value = what I'm looking for, I set BreakFlag = false.
Then, to continue without further breaks, simply set breakflag = true and run!
I use it a lot when I'm testing code for these u
Events = True
Exit Sub
ErrHandler:
MsgBox Err.Description
Resume ExistHandler
End Sub
Paul
-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times
the increment to avoid duplications.
But would prefer NOT to have 1440 increments (24 hrs * 60min/hr)
Paul
-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the peo
.EntireColumn.Insert _
Shift:=xlToRight, _
CopyOrigin:=xlFormatFromLeftOrAbove
Cells(1, inx + 1).Value = MyArray(inx)
Exit For
End If
Next inx
Wend
End Sub
Paul
-
“Do
You mean like:
Sub SelRows()
Dim R
For R = 1 To 10
Range(Cells(1, "A"), Cells(R, "A")).EntireRow.Select
Next R
End Sub
??
Paul
-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In al
ot;%".
if the cell already IS the value, just change the cell format.
otherwise, we're going to need more information.
Paul
-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times
Check out the attached file.
I created a macro called "Cleanup".
It unmerges/unwraps cells, then deletes any column that has no entries.
Paul
-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you c
Are you wanting someone to write the entire macro
or are you simply needing help in automating the email?
Paul
-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the
What email application are you using?
Paul
-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
, or column "P".
so you could use:
If FileFolderExists("\\cf3.pepsico.pvt\psra\Output\BI4\PSRA Day Tracker -
Exec.mhtml") Then
Cells(4, Weekday(Date) + 14).Value = "X"
End If
but then again, this may not be the logic you're trying to encode!
let m
Well, that answers that question.
you have it in the ThisWorkbook module, which is correct.
Which means that it probably actually RAN, but
Weekday(Date) = 2
will return "false" because today (thursday) is Weekday #5
Paul
-
“Do all th
OK, I'll take a look at this as soon as I get some things I'm PAID to do taken
care of!
Paul
-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people y
Here is a formula solution.
The macro solution was to use a "custom function".
I'll work on that.
Paul
-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To
What type of solution are you looking for?
That is:
If I make a custom function using VBA, would you be able to modify it to suit
you?
or do you want the solution using Excel formulas?
Paul
-
“Do all the good you can,
By all the means you can,
In all the
's more "back story" behind
this that makes it so this technique will not work, let me know.
Paul
-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people
a watch on weekday(date), as you step through the macro, it will
show that the value is 4 (not 2)
Paul
-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you c
le,
selecting the right-hand pull-down will show you the macro names and allow you
to "jump" to the macro instead of scrolling to them.
(really helpful when you have 5000 lines in 10 macros in a single module!)
In the Thisworkbook and Sheet modules, the event macros that you've writte
ullString Then
Set f = fso.getfile(strFullPath)
If (DateDiff("d", f.datelastmodified, Now()) = 0) Then
FileFolderExists = True
End If
End If
EarlyExit:
On Error GoTo 0
End Function
--
I kn
GREAT!!
Now I think I know where to look.
Let me write a somewhat longer explanation and get back to you.
Paul
-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all
with creating a Workbook_Open
event macro.
Unless you're trying to put the macro in a Sheet module instead of the
ThisWorkbook module.
please provide more information for what is happening (or not happening) and
what you WANT to happen.
Paul
-
“
use something like:
Workbooks("Orders.xls").Sheets("Sheet1").ListObjects("Table1").Range.AutoFilter
_
Field:=2, Criteria1:=MYNUM
Workbooks("Stocks.xls").Sheets("Sheet1").ListObjects("Table1").Range.AutoFilter
_
Field:=
the rest?
(third row move up to row 2 or ??)
Is there a limit to the number of rows?
what happens when you exceed the size of the userform?
do you want the macro to increase the size of the userform?
Paul
-
“Do all the good you can,
By all the means you
hen
or add a variation of this to your parent macro.
(which runs if the filefolderexists function returns "true")
Paul
-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times
I don't see an attachment.
so I have no idea what your code looks like!
Paul
-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as eve
erExists("\\cf3.pepsico.pvt\psra\Output\BI4\Exec Dollars - Current
Period.pdf") and weekday(date) = 2 Then
I usually prefer to use more ( ):
If ((FileFolderExists("\\cf3.pepsico.pvt\psra\Output\BI4\Exec Dollars - Current
Period.pdf")) _
and
ror GoTo 0
'--
Paul
-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
-
When you open the new workbook, it will be "active"
using something like:
WbName = activeworkbook.Name
will store the name of the open workbook.
later:
Workbooks(WbName).Close SaveChanges:=False
will close that workbook
Paul
-
“Do all th
ing2013.xlsm").sheets("Temp").Range("A1:I100")
= _
Workbooks(Range("BusinessOrganizerMapping2013.xlsm!A7"))").sheets("Temp").Range("A1:I100"
Paul
-
“Do all the good you can,
By all the means you can,
not sure what the purpose of the if/elseif/endif statement is.
You could always use:
For Each Rng In Range("B:B")
If (Rng.Value = "") Then Exit For
Cells(Rng.Row, "A").Value = Rng.Value
Next Rng
Paul
-
gs in Windows,
Excel can display today's date as 11/10/2014 or 10/11/2014
depending on whether your default date format is mm/dd/
or dd/mm/
The fact that the solution provided is being displayed as a serial number
merely means that your cells are formatted as "general" inst
>
>
> Hi all,
>
I'm Paul from Australia.
A former corporate IT trainer and small solution developer interested in
bringing my knowledge up to date.
My other interests include photography, web development, cga and 3d
modelling.
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in
(File.Name).Sheets(ShtNo).Copy _
After:=Workbooks(NewWb & ".xlsm").Sheets(Workbooks(NewWb &
".xlsm").Sheets.Count)
ActiveSheet.Name = wbName
Workbooks(File.Name).Close savechanges:=False
End If
End If
Next R
'---
t;).Sheets.Count)
ActiveSheet.Name = wbName
Workbooks(File.Name).Close savechanges:=False
End If
Next File
'
Application.ScreenUpdating = True
'
Workbooks(NewWb & ".xlsm").Clos
with "Week.." or all are type .xlsx?)
We can specify the files on the worksheet:
11000
11001
11002
or we can simply process all files in the folder.
I'll make some sample files for testing and then put together a macro for you.
Paul
-
“Do
How do you want to do this?
I simply highlighted the ū and hit ctrl-c
then selected all cells and hit ctrl-h (to find/replace)
in the find box, i hit ctrl-v to paste the ū
in the replace with box, i inserted u
and then hit ReplaceAll.
it worked just fine.
Paul
se two formulas down.
We could do something similar with VBA.
Create a Dictionary Object (array) to store the ID/Name combinations.
If the combo exists, mark each record as "Failed", otherwise, add the unique
combo.
Paul
-
“Do all the good you can,
501 - 600 of 1741 matches
Mail list logo
