e appropriate
variables.
Does this help point you in the right direction? or are you needing detailed
assistance? 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
I have a list of Roll nos of 1000 students in a excel sheet. After
publication of exam result in the website, we used to put the roll nos in
the input textbox of the website and get the corresponding result which is
then copied and pasted in a file. The same procedure is adopted for the all
If you already have code that "refreshes" the list, then you can create a
Workbook_Open event that will execute it when you open the workbook.
Paul-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the plac
you can use =Max(C8:C11) and (Min(C8:C11)
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
l shows a "not
responding" message, even though it's still running.People often interpret this
as a "crash".
If you can provide a great deal more info (with sample data?) then I/we would
be glad to help.
Paul-
“Do all the good you can,
veCell.Row, "B").Value Set FSO =
CreateObject("Scripting.FileSystemObject") If Not
FSO.FolderExists(sFolder) Then MsgBox "Specified Folder Not Found",
vbInformation, "Folder Not Found!" ElseIf FSO.FolderExists(sFolder) Then
ebuild
my laptop.
But I'd prefer to determine what is CAUSING this, in case another user of
my application encounters the same issue.
any ideas?
thank you,
Paul
--
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 page of
ata
and load unique values into an array. Then, in the initialize event, set the
combobox to the array.
I can try to put together an example.
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 a
").Value) =
Dict_E.Item(Cells(nRow, "A").Value) & "+" & Cells(nRow, "E").Value
' Error with =dc.item instead of =dc3.item
End If
End If
End If
Next
Sheets("Sheet2").Select
so now, you have an array of data.
and a query that returns a station name and number.
so what are you looking up?
are you trying to find the row with the matching station name,locate the
matching value and return the day/shift??
Paul-
“Do all the good you
Are you saying that the SQL query returns:
Reject counter for Zone 0, Station 12 (CheckPresence)
but the Station Name is only: Station 12 (CheckPresence)
??
Paul-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places
Your first option is pretty simple.
replace your line:FolderName = "C:\test\"
with:FolderName = ThisWorkbook.Path & "\"
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,
(FolderIndex, "000") &
"\"
If (Not fso.folderexists(NewFolder)) Then
MkDir NewFolder
End If
fCnt = 1
End If
fso.movefile FolderName & FileNm.Name, NewFolder
Next FileNm
MsgBox "Fini
that for Station 18, count 172 is found on Thursday, Day shift.But
I don't find the Station 12 count of 82 in your example... so perhaps I'm not
interpreting it correctly.
and, what is it you're trying to look up?
Paul-
“Do all the good you can,
By
thing else?
how do you read from it?Do you generate a report electronically?
I think we need more detail to help properly.
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,
on how you summarize the data.
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
the files and collect some data from each file (data extraction)But then you
said: "in a new folder".
Are you saying that you want to move (or copy) the files that are in your excel
list from one folder to another?
If so, it's easily done.
If not, then I'll need to know what you actual
moved the first 200 files into it,then created Folder_002
and moved the next 200 filesand so forth until all files were moved..
would you be able to follow the macro and modify it to fit what you REALLY
want? Paul-
“Do all the good you can,
By all the means
Let me test it. 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
and
21:00 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
-
of duplicating hours and have to deal
with that.
I'd suggest using a macro.
If a macro was created, do you know how to implement one?
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 t
d the cell formatting.
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
.
or many other options.
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
is in
mm/dd/yy format,but his system date is set to yy/mm/dd format
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
,2)))
changing the cell format to mmddwill result in values like:
20151012 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 a
te for EACH ROW.You probably want to
do something like:
dim testDate as DatetestDate = Application.InputBox("Enter Date with this
format mm/dd/")
then, use something like:
If (DateDiff("d", testDate, Date) = 0) then
Paul-
“Do all the goo
the whole thing.
You probably ought to look for SAP forums.
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
!Xwhich VBA
interprets as a string instead of an error.I've also had this not work
properly, in which case it was easier to test for an error.
So, I'd suggest looking at the data and see what value is present in
Data.Row,"Z".
Paul-
“Do all the goo
le in the UBound() function to be an
array.If the variable is a "Variant", then the Ubound() function would indicate
that the variable type does not "match" the type required.
I suspect that you INTENDED the statement to be:
Sheet2.Range("b2").R
to run the userforms that allow changes, etc.
Do you want assistance with this? or are you looking for someone to do it?
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
of a day is complete, or .25 of a day.at noon, 1/2 of the
day is gone.
7:33am is .3127024306 of a day.
so, November 12, 2015 at 7:33am is 42320.3127024306
Paul-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places y
What you've provided doesn't describe the logic.
Evidently, you want the Q3 figure and Q4 figure for each segment,but is the
column intended to shift for each subsequent segment?
Will the original data set be sorted by segment?
If so, it would be fairly easy to generate the code.
Paul
readystate is used to determine if the page has loaded completely.
With Excel, those things don't happen simultaneously (unless specifically
DESIGNED to do so).Once the file is loaded, THEN the workbook_open macro
executes.
So, the fact that the Workbook_Open event runs means that the file has lo
dy has an answer.
So, if you have 90,000 vlookups, and change the heading, it executes 90,000
times.
I would definitely use a Dictionary object.
HOW I'd use it would depend on the relationship between the files
If you could send me some sample files, I'd be glad to throw some
op-right of the pane, the pulldown will list other "event" macros
available.
also, an alternative is to create a macro called:sub Auto_Open()
this goes in a "standard" module.It works similar to the Workbook_Open event
macro.
hope this helps.
Paul--
excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
match data(Paul).xlsx
Description: MS-Excel 2007 spreadsheet
Dict_Activity.keys
and loop through the array and report each document/record.
I 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 places you can,
At all the t
would like to do is to copy the formating of other columns to
automatically change the highlight color depending of the week and month
column.
Something like "if this cell is highlighted, so I got the same highlight".
Thank you for your help and sorry for the approximate english.
Paul
-
e and returning the value of that range.
So, I THINK that if you change: Dim_Buy = Range("Num_Buy")Dim_sell =
Range("Num_Sell")to: Num_Buy = Range("Num_Buy").ValueNum_sell =
Range("Num_Sell").Va
I don't understand.You wanted to split a string into columns using "-" as the
delimiter.What does that have to do with "one character, not three"?
how does that apply? Paul-
“Do all the good you can,
By all the means you can,
In all the
Can you provide a sample file showing what you'd like?Are you looking for
Conditional Formatting for the column? Are you looking for a macro to highlight
the date?or even a Change_Event macro to format the
column?Paul-
“Do all the good you can,
By all
-10-2015, Excel doesn't compare it to the CELL format, but only
the WINDOWS format, and will conclude the value is TEXT.
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
End If
Next ColNo
End If
Sheets("Sheet 2").Select
End Sub
If you want to add additional selections, you need to add to the If/or/endif
section.
If you want it to search another sheet, then you COULD have a pull-down of
available worksheets that, when selected, w
lumns.So I often
have to manage large amounts of data.
If you can give me a sample, I'd be glad to take a look at it.
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
can you send me your file so I can locate the issue?
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
Did you open the file I attached?(here it is again) The find_Num function is in
Module1
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
Dates should be displayed as the Windows settings dictate.
So, when *I* open it, the date displays as 11/2/2015.
you can change it to 02-Nov-2015 if you want, and the dates should calculate
correctly.
Paul-
“Do all the good you can,
By all the means you
ange Event macro
updates the displayed columns. 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
Attached, I created a function to locate the most recent occurrence.=Find_Num()
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
l workbook can be Read-Only, but that doesn't stop you from running a
macro, nor should it cause an error.
So, WHEN does the read-only error occur?What line of the macro?
Paul-
“Do all the good you can,
By all the means you can,
In all the ways you c
to "repeat" step 1) (open the workbook)it gives you warning of
some kind.
What is the warning?
A "Read-Only" warning doesn't have anything to do with the Workbook_Open event
macro.
It happens before the event handler is activated.
Can you send me a screen shot
Do you have write permission to the folder on the server? (try to create a new
file in the folder)
If you do not have write permission to the folder, any file opened will be
read-only.
Paul-
“Do all the good you can,
By all the means you can,
In all
omething from
this date you can do so.also: your "month" value in column "A" can be:
date(Year(P2),month(P2),1)and DISPLAYED as mmm-yy
your summary table is similar.
Paul-
“Do all the good you can,
By all the means you can,
In all t
CH product.
In cell J3, I would use:
=SUMIF($A$2:I$2,J$2,$A3:I3)
Copy it to Column K and down.
(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 peop
led WS_Change
Now, to update the macro only requires making changes to the macro in the
"standard" module.
Paul
On Friday, October 23, 2015 at 1:30:29 AM UTC-4, Pravin Gunjal wrote:
> Hi Friend
> pl help me out to replace a macro in all sheets of the same file. Or any
> ot
)+SUM(D:D)+SUM(F:F)+SUM(H:H)
if that's NOT what you're looking for, please explain further.
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
Have you tried recording a macro and then using "Remove Duplicates" from the
Data tab?
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 ca
ect: Re: $$Excel-Macros$$ VBA code for delete duplicate rows
Hi paul,
i tried , but i am looking for a code, so i can customize , if i recorded with
macro i may get hard code values.
Request you to provide code.
RegardsMani
On Thu, Oct 22, 2015 at 11:28 PM, Paul Schreiner <schreiner_p...@att.ne
What kind of solution are you looking for?You said you're new to Excel, so does
that mean you're not comfortable working with VBA macro programming?
You said you're doing it "manually".How?what steps do you go through?What do
you want the end result to look l
/st 8:00:00 (yours will be different)
Run that .bat file and see if it creates the Scheduled Task correctly.
Once you have this .bat file working properly, then we can work on writing the
VBA that creates and runs this bat file.
Paul-
“Do all the good you
a .bat file and schtasks as described below,it will make an entry
that you can view and modify in the Windows Task Scheduler.
So, you can use VBA to create and run a .bat file that can make or change
entries to the Task Scheduler.
Paul-
“Do all the good
te the .bat file.
I think to stop your program at a specific time you could use:
schtasks /end
I'm sure you can google some examples.
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 t
one I especially like, and it wouldn't take me
long to do it,but it would take me longer to build a sample data sheet.
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
).Cells(R, "A").EntireRow.Hidden = False) Then
strCountry = Sheets("Criteria").Cells(R, "A").Value
strState = Sheets("Criteria").Cells(R, "B").Value
strDistrict = Sheets("Criteria").Cells(R, "C").Value
KeyField =
g that many formulas is VERY slow.
I could easily write a short macro that would produce the summary.
Can you send me a sample file? or I could try to create a bunch of data to test.
Paul-
“Do all the good you can,
By all the means you can,
In all the ways you ca
Izhar:
My "hijacking" comment was directed at Abhishek Bhallawho seems to have asked
his own question about combining cellswithin YOUR question about vlookup.
(which serves to demonstrate the "confusion" that I mentioned!)
Paul-
“Do
Sheet).Range("A1").Value = "FileName"
Sheets(RepSheet).Range("B1").Value = "Path"
Sheets(RepSheet).Range("C1").Value = "Size"
Sheets(RepSheet).Range("D1").Value = "Date Created"
Sheets(RepSh
xplain WHY I selected a specific approach.
Speaking of which... back to work!
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 problem with that approach is that it would not maintain the original
folders.It would move them to a SINGLE folder.
I strongly suspect that is not what is desired.
Paul-
“Do all the good you can,
By all the means you can,
In all the ways you can
Abhishek,
snarky...I like it!
:)
Paul
From: Abhishek Jain <abhishek@gmail.com>
To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
Sent: Wednesday, October 7, 2015 2:37 AM
Subject: Re: $$Excel-Macros$$ filter and copy/paste in new workbook and save
it on des
---
' Save new file as "thisisthenameofanewworkbook" and close
'
ActiveWorkbook.SaveAs _
Filename:="C:\Users\(youruseridgoeshere)\Desktop\thi
column F to column G,
the Lookup_value would have stayed the same, and you would simply change the
column index returned from 6 to 7:=VLOOKUP($A4,DATASET,7,FALSE)
Paul-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In al
Is this related to "VLOOKUP NOT WORKING"?
I suspect you've "hijacked" Izhar's thread.
see forum rules #2:> 2) Don't post a question in the thread of another member.
I'll be glad to help, but don't wish to confuse another discussion.
Paul---
= tMsg & ActiveSheet.Cells(1, C).Value
End If
Next C
If (Not tMsg = ActiveSheet.Cells(R, "A").Value & ": ") Then
msg = msg & Chr(13) & tMsg
End If
Next R
MsgBox msg
End Sub
Paul
what error?
I added it to my VBA project.I think you need adodb.Connection(see the ".")
Dim con as new adodb.connection 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 tim
You're welcome!Any time! 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
Sure!!! I can try.
See below:
From: Mandeep Baluja <rockerna...@gmail.com>
To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
Sent: Thursday, September 24, 2015 12:37 AM
Subject: $$Excel-Macros$$ Explanation for a code
Dear Paul,
Could you please
Please not Forum rules #3:
3) Don't post questions regarding breaking or bypassing any security measure
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
)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
ost common name in another
column, would it be better to simply change the name in the existing column?
it would be better for me to have a sample file to work with.Otherwise, I'd
spend as long, or longer, trying to put together sample data that may not even
match your
eria)
in D2, put:=COUNTIFS(A:A,A2,B:B,B2)and copy down.
Then, sort by column A and D (with D sorted largest to smallest)Then, in column
C, put: =VLOOKUP(A2,A:B,2,FALSE)and copy down.
if you need a more elaborate or macro solution, let us know.
Paul-
“D
2240.4
that means that you can subtract two date/times and get the number of days and
the fractional part of days between two times that are on different dates.
hope this helps.
Paul-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In
e the contents and shift the cells,or even to
delete an entire row, then you'll need to modify the macro.
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 t
clarification
questions BACK as answers) 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
ot;,
your confusion is probably with the definition of "sub".
It is NOT "subordinate"as if it were:1) Procedure a) Sub-Procedure
It refers to a SUBROUTINE:
As in:
Sub Test MsgBox "Hello"End Sub
If you need help with the VBA debugger or specific help with a project, let
than a
date comparison.
When comparing Strings, in this case, it would compare the first characters:"9"
and "S"This isn't what you're looking for.
You need a DATE comparison.I'd suggest Datediff()
If ((DateDiff("d", Range("T2").Value, Now()) >= 0) _and (DateDiff
ue =
Sheets("data").Cells(RowSource, "A").Value
Sheets(Teacher).Cells(RowDest, "B").Value =
Sheets("data").Cells(RowSource, "B").Value
End If
Next RowSource
Sheets("data").Select
Application.ScreenUpdati
Hi,
Is there anyone to help?
On Mon, Aug 31, 2015 at 5:18 AM, Soumyendu Paul <soumyendu...@gmail.com>
wrote:
> Hi,
> In attached file there is a date column containing all dates of
> September month. I want to sort data like 1Sep, 2Sep, 3rd Sep irrespective
> of year using V
Are you saying that you'd like a macro that:1) Prompts you to select a
folder.2) Opens all Excel files in the selected folder.
??
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
to help.
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
Hi,
Want to search header Name in attached file through vba but unable to do
with find method. Please help
Regards,
Soumyendu
--
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 page of this forum @
case.
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
sheet, you will have to
copy/paste Values on the petty cash sheet.
An alternative would be to write a macro that updates only the single record.
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
.
Then, as long as the new sheets are named in the proper
format:transaction_mm.dd., then the Petty Cash sheet will update
appropriately.
If you can give me the proper sheet name format, I could make sure the formula
is corrected.
Paul
-
“Do all the good you
not be the one that writes
10,000 lines of code a day, it could be the one that just came across your
problem yesterday and knows how to fix it!
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
telling the Filesystem Dialog
method to return the name of a selected FILE,msoFileDialogFolderPicker returns
the name of a selected FOLDER.
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
and use:
ActiveWorkbook.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=C:\temp\Displayed_Sheets.pdf, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
Paul
, you're trying to find the column number of the last column with a
heading in row 8, you could use:
Lastcolumn = Cells(8, G).End(xlToRight).Column
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
Please notice the Forum Rules:3) Don't post questions regarding breaking or
bypassing any security measure.
If you are the owner of the application, there are places to go to get it
unlocked.Simply Google something like:
excel vba password recovery
I found 357,000 hits...
Paul
) _ and (sht.name Summary C) _ ) then
sht.select movedata end if next Sht
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
301 - 400 of 1592 matches
Mail list logo
