quot; the value.
I usually do something like replacing a space with a space.
excel would then recognize the string as a date and store it as a date.
But I have no idea if GoogleDocs does that.
Paul
-
“Do all the good you can,
By all the means you can,
In
= tDate & " " & sTime
End Function
using the above fundtion, you could either enter it into the spreadsheet:
=ReplaceStringDate(A2)
or create a loop and call the function:
Sub ConvertAll()
Dim R
For R = 2 To 15
Cells(R, "B").Value = ReplaceStri
should have "Failed" in some column?
or only in row 25,26?
or some other test or result?
and:
"check if the vendor names are the same or not"
The same as what?
check to see if you have duplicate names?
or??
Paul
-
“Do all the good you can,
Do you want a macro that counts the "yes" values and copies the row?
or woujld it be enough to copy the client ID's to the second page,
then use a =Countif() function to count the "Yes" values and put the result in
the second page.
Then, simply fiter on rows
ByVal WkSh As Object, ByVal AmAt As
Excel.Range)
So, I assumed you were using VBA (since this is the Excel VBA
SheetSelectionChange event)
But the <\CODE> syntax isn't VBA, so nothing I said would apply.
sorry.
Paul
-
“Do all the good you
quot;P1" and "Q1" to "N1" and "O1"
also probably change
SourceData:=wsScratch.Name & "!R1C1:R" & nextrow - 1 & "C" & 15 + NUM_SHEETS,
to
SourceData:=wsScratch.Name & "!R1C1:R" & nextrow - 1 & "C" & 1
Dict_Sheets(tColor)).Sheets.Count)
Else
Sht.Copy
After:=Workbooks(Dict_Sheets(tColor)).Sheets(Workbooks(Dict_Sheets(tColor)).Sheets.Count)
End If
Next Sht
Application.ScreenUpdating = True
End Sub
Paul
-
“Do all the good y
then shifted the Zip/phone/shipVia/Company columns.
it's allso missing the "HS" column.
adding the necessary column headings should fix your problem.
Paul
-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In
are simply a matter of learning the syntax and how
the compiler works.
let us know if you have a question and we'll do our best to answer.
Also, keep in mind that the members of this forum are from all over the planet!
So, sometimes your question might be
t is, if you select cell A9, Target.Address is $A$9
So, if you want to test to see if the selected cell is A9, you could use either:
If (Targ.Address = "$A$9") then
or even:
If (Targ.Row = 9) and (Targ.Column = 1) then
Paul
-
“Do all the good
rn a
success/fail result.
I suspect that the VBA programmers went with the latter.
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 peopl
g?
which you can do with Application.FileDialog(msoFileDialogFilePicker)
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 ev
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,
As long as ever you can.” - John Wesley
-
From
es of vba code in 12-14
applications (workbooks).
But if you were to look at my early work, I know they would look juvenile.
We're always growing. Even the ones that don't think they have anything else
to learn!
take a look and let me know how I can help.
Paul
---
hen the End Date combobox is changed)
Your macros are about 215 executable lines of code(excluding blank lines)
My approach takes about 110 lines of code, and is much easier to follow (in my
opinion).
I didn't incororate my original solution to your latest file, but I can if you
wish.
hoose.
If you don't like it, or you don't want to dive into the world of macros, then
nevermind.
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
If (Not FlagDup) Then
C = C + 1
Sheets("Summary").Cells(R, C).Value = tString
End If
End If
Next rng
End If
Next sht
End Sub
Paul
-
“Do all the
ERPRET it?
without more information as to what you want to accomplish, it would be almost
impossible to guess enough to provide any meaningful assistance.
Paul
-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places
Cur_SysSep
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
his number represent?
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
re are 24 hours in a day, you need to multiply this value by 24
to get:
11.417 hours
(which is .417 HOURS, not 41.667 minutes!)
let me know if you need help applying this logic to your workbook.
Paul
-
“Do all the good you can,
By all the means you
I thought I'd sent this, but it looks like I did not.
If you create an AutoFilter, then your buttons can simply apply the filter
(as 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 ca
an .exe format, since they normally would require the application in order
to run!
So, if you can explain what you have in your VBA and what you're trying to do
with it, perhaps we can help accomplish your task, just not the exact way
you've described.
Paul
Why were you not able to change the captions?
Do you need help with 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 all the people you can,
As long as ever
What do you know of Excel and VBA?
This is a pretty simple exercise.
Simply insert an ActiveX button.
change the Properties so that the name is: Btn_Filter.
Select "View Code"
add this to the macro:
Private Sub Btn_Filter_Click()
If (Left(UCase(Btn_Filter.Caption), 4) <> "TYPE") Then
xt, in Cell J3 I inserted:
=COUNTIF(K3:Q3,"start")-1
and copied it down.
This counts the number of tims a task was STARTED, then subtracts 1 to count
the number of starts except for the first.
Paul
assigned the macros to the buttons.
It SEEMS to do what you're asking.
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
front of you,
you come across answers to questions you haven't even asked (yet)!
let us know if you have trouble, or need an explanation as to how (or why)
something works.
Paul
-
“Do all the good you can,
By all the means you can,
In all the ways y
played when you hit the
"BSE 30" button?
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 peopl
ies whether you
want VLOOKUP to find an exact match or an approximate match:
* If range_lookup is either TRUE or is omitted, an exact or approximate
match is returned. If an exact match is not found, the next largest value that
is less than lookup_value is returned.
Paul
-
"OK","")
requirement #2:
to test if the date is overdue, use:
IF(E3-TODAY()<=0,"OVERDUE","")
Combine the two to test if the status is complete, or cancelled,
then test to see if the date is past due use:
=IF(OR(H3=&
isplays the userform.
the button would then have to update some other data source, like a file in a
network folder.
then, provide a link in the email for THAT workbook.
Depending on your network environment, you may have a messenger client that you
could use to send a notificatio
to do the whole thing in VBA (without using VLookups)
but, since it only takes a minute and 15 seconds, 7% wasn't enough to worry
about.
I wasn't sure how you wanted to run the macro (called Create_GradeSheets)
Paul
-
“Do all the good you
Absolutely this is possible.
it looks like a fun exercise.
do you have any experience with VBA?
I'll take a look this morning and try to get back with you.
Paul
-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In al
e("$A$1:$A$65000").AutoFilter _
Field:=1, _
Operator:=xlFilterValues, _
Criteria2:=Array(2, Yesterday)
actually manages to (I think) cast the Array() as a Variant data type.
So..
in THIS case, it didn't really matter what data type I used.
If I used:
Dim Yesterday
Yesterday)
but, since I don't know what you're trying, I don't know if this is even CLOSE
to being what you're looking for!
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,
l get that uses these terms is to use Excel
AutoFilters and then look through the sheet and test to see if the cell is
visible.
which is probably not what you're looking for.
Paul
-
“Do all the good you can,
By all the means you can,
In all the ways you
ENAME_HERE
group by Cust_ID
BTW: You asked a SQL question in a forum of Excel/VBA users.
Does that mean that you're looking for a VBA solution?
If that's the case, there's WAY too much information missing.
Paul
-
“Do all the good you can,
simpler and easier to maintain.
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
er of
tabstrips change to accomodate the new userform size.
Has anyone had any experience with tabstrips or multipage strips?
I found one semi-usable example with a Google search, but it still feels
like I'm starting from scratch.
any suggestions?
Paul
--
Are you =EXP(E:RT) or =NOT(EXP(E
can you give me an example?
I tried a couple of things to see if it could get VBA to act up.
No luck... seems to work fine in Excel 2010.
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
extRw & " < """", D" & NextRw & ","""")"
so your VBA code looks like:
ActiveSheet.Cells(i, 2).Value = "=IF(D" & NextRw & " < """", D" & NextRw & ",
""&q
alue of Cells(32 + j, 3 + i) in the Data sheet,
you would either use:
Worksheets("Data").Cells(32 + j, 3 + i).Value
or
Sheet1.Cells(32 + j, 3 + i).Value
Paul
-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the pl
In your case, what constitutes "similar"?
If reading left-to-right, if the two concatenated strings match the first X
characters, then is it "similar"?
Paul
-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In
what you were trying to accomplish.
Otherwise, the missing information relies on assumptions made by the mind of
others. Which, in some cases can be an adventure in itself!
let us know if you need additional assistance.
Paul
-
“Do all the good you can,
By all t
to match the fileformat of your file.
the easiest way to figure this out is to record a macro in which you copy a
sheet to a new workbook, save it as a new filename and type and close the
workbook.
Paul
-
“Do all the good you can,
By all the means you can,
Are you saying that:
1) you'd like to use VBA to insert the =IF() statement?
2) or that you'd like VBA to perform the test and enter the result?
3) or that you'd like a VBA function to perform the test and return the result?
Paul
-
“Do all t
se(EXT), 3) = "XLS") Then
...
end if
next f
end if
of course, if you already know the filename,
you can use:
if (fso.fileexists(filename)) then
Paul
-
“Do all the good you can,
By all the means you can,
In all the ways you
?
For a given cell (D4):
If the cell is an excel formula (like =vlookup() or a calculation),
Range("D4").Formula
will return the formula but
Range("D4").Value
should return the resulting value.
I'm not sure how you're going to get past using an event function though.
I
Are you saying you need to set NextRw to 6 if it's the first set?
if so, after
NextRw = .Cells(Rows.Count, 2) _
.End(xlUp).Offset(1, 0).Row
add:
If (NextRw < 6) then NextRw = 6
Paul
-
“Do all the good you can,
By all the means you can
h is to the file and use the VBA filesytem object to copy the file.
If it actually is on an intranet web page, then it can still be accomplished,
but it's a little more involved.
Paul
-
“Do all the good you can,
By all the means you can,
In all the
;X") Then
.Cells(NextRw + 0, "D").Value = "-"
Else
.Cells(NextRw + 0, "D").Value = Me.txtPointsReceived.Value
End If
Paul
-
“Do all the good you can,
By all the means you can,
In al
quot;, ForAppending, True)
f.Write "Hello world!"
f.Close
You would open the text file, then loop through your worksheet and write each
line.
Usually, I build a string variable from the worksheet row and write the string.
Paul
-
“Do all the good yo
Sub
Sub Save_Macro()
ActiveWorkbook.Save
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,
As long as ever you
to .txt
so, what am I missing in this 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 the people you can,
As long as ever you c
example,
which name gets tested FIRST is important:
If you were to reverse the first two names, then the selection wouldn't work as
intended.
Paul
-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all th
ForecastLastUpdated_*.xls*
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
tion.
so, the first step would be to convert your times to Excel time and proceed
from there.
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,
T
n changing DATA"?
So... making LOTS of assumptions regarding unanswered questions,
are you saying you'd like a Change_Event macro in which whenever any data in
columns I-J on the sheet is changed, the macro will copy the records in columns
I-J that have a non-0 value in column J to colu
ow.
Like:
Do you know how to write/modify/maintain macros?
Where is the source data? (workbook/sheet)
Where do you want the output displayed?
How do you want to execute the macro (button? Change Event?)
so... does any of this help?
Paul
-
“Do all the good
Welcome!
I am Paul Schreiner, working at Allison Transmission in Indianapolis, Indiana.
I maintain over 120,000 lines of VBA code in multiple manufacturing
applications.
If/when you have a question, bring it on!
Paul
-
“Do all the good you can,
By all
m!
If you simply type "data types" in the vba help box, you'll find that there are
13 types:
Boolean
Byte
Currency
Date
Double
Integer
Long
LongLong
LongPtr
Object
Single
String
Variant (default)
As for interview questions: simply Google:
vba interview que
.
instead of using SaveCopyAs to change the file type.
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
le itself.
It's pretty convoluted, and deals primarily with "memory management".
for your purposes, it just means you need a space.
" & wrkbk & "
Paul
-
“Do all the good you can,
By all the means you can,
In all the ways you
e current file,
use SaveAs to save as a different filetype.
Open the original file
close the "New" 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
iving from designing
solutions using Excel
and often protect their "intellectual property" using passwords.
A group (such as this) has no way of knowing if you are the ACTUAL owner of the
Excel workbook,
or merely trying to unlock someone else's work.
sorry,
Paul
(RC[-3],'[" & wrkbk & "]Sheet1'!C1:C8,8,0)"
ActiveCell.FormulaR1C1 = sVar
Using sVar is helpful in debugging, simply because you can display the value of
sVar to see if the string is being built properly.
But it is not required.
ActiveCell.FormulaR1C1 = _
"=VLOOK
what email program are you using? (Outlook?)
Is "File_Name" and "File_Name2" the full names? or is there also a file
extension?
(so, in your sheet, you have "File_Name" but the actual filename is:
"File_Name.docx"?)
Paul
--
and execute accordingly.
If K1 has a pull-down for selection, I believe you can use a Calculation event
to trigger the filter.
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,
simply use the listbox to select the columns to display and hide those
not selected?
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
I forgot to add:
saving both files in the same format (.xls, .xlsx, .xlsb) would accomplish the
same result.
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
blem with yours.
So,
Could you send me a sample set of files?
you don't have to send all 100.
alpha, beta and gamma (or George, Paul and Ringo) would suffice.
I realize the data may be sensitive, so you may have to "change the names to
protect the innocent".
But unless I can s
000 rows.
In your vlookup, specify the rows (some number beyond the maximum) like:
=vlookup(j2,[MData.xlsx]Sheet1!$A$1:$B$65000,2,False)
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
I'd use something like:
With the date to consider in A1, use:
=(YEAR(TODAY())-YEAR(A1))*12+MONTH(TODAY())-MONTH(A1)
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 yo
t;Delhi")
The macro would then copy all of the "Delhi" records to Sheet1 or Sheet2?
If that is your goal, I might suggest instead of copying the worksheet to the
current workbook, simply copy the data to the Master sheet instead.
If you could send me (directly?) a
e the difference.
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
of days between the two dates.
the difference between
Friday, August 07, 2014 7:15 AM
Friday, August 07, 2014 3:57 PM
is 0.362500 days, or
8:42
(which means I logged in at 7:15am and logged out at 3:57pm,
so I was at work for 8 hours and 42 minutes)
hope this helps.
Paul
--
you,
you'll have to provide much more information.
How many rows of data?
How many columns?
What do you want to do with the resulting text string?
What do you want to do with the original data?
How do you expect to maintain it?
Paul
-
“Do all the good
I'm not sure how this relates to pdf files.
Or has the original discussion thread been "highjacked"?
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 do you mean by "nearest".
Closest without going over?
or nearest absolute?
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,
ime for the
corresponding "OUT" ring.
I can write a macro that reads from the earliest time to the latest,
and look for matching "Reader" and show the time difference between in/out.
Then, if there is no "out", then indicate the missing r
(F3,2)
in your data match sheet, simply use a VLookup:
=VLOOKUP(B2&C2&A2&E2,Source!A:C,3,FALSE)
and copy down.
However, in your samples, none of these criteria match data in the Source sheet.
??
Paul
-
“Do all the good you can,
By all the
These quote are not allowed in VBA.
simply change them to (") and you should be fine.
Sub GuessName()
Msg = "Is your name " & Application.UserName & "?"
Ans = MsgBox(Msg, vbYesNo)
If Ans = vbNo Then MsgBox "Oh, never
w number in a
Dictionary Object.
Then, I read in the "daily" file.
For each row, using the Record index, I locate the row number from the
Dictionary Object and update the Master file.
>From what you've given us, there's way too much missi
nd protect the sheet.
You can then use a Workbook_Open event to look at the user's login id and
unlock the sheet for those that should have access to the column.
Paul
-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the pla
Are you saying you want to copy the CONTENT of the Word Documents to an Excel
workbook? or append each of the Word Documents to a new Word document?
Paul
-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you
Oops, yes you included the range in the indirect() string.
so, the solution was simply using the text() function to convert the date
number to a string
Paul
-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places
o a string that resembles your sheet names, I used:
TEXT($A5,"dd-mm-")
Then used (nearly) what you did originally with the indirect() function.
but, I believe you did not include the Range (C18:C40) within the indirect
string definition.
Which you need to do.
For your sumif(Indirect function in cell B5, try:
=SUMIF(INDIRECT("'"&TEXT($A5,"dd-mm-")&"'!C18:C40"),B$3,INDIRECT("'"&TEXT($A5,"dd-mm-")&"'!F18:F40"))
(It should copy to D, F, H, J also)
P
"
But, I tested to see if this error occurred if the range name no longer existed.
Instead, I got:
"Run-time error '1004': Application-defined or object-defined error"
Slightly different, so my guess is that either the range
"CalPERSImpliedInterestRate" or "Bench
hat represents one currency and you want it CONVERTED to
another currency, you have to have the exchange rate and do the math.
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
You're welcome!
I just figured that if I simply supplies the formula, there would be some that
would like to see how it came together, so I'd just take care of that right
away!
Paul
-
“Do all the good you can,
By all the means you can,
In al
N()-4)-$C3,$D3)=0
within an OR() statement:
OR((COLUMN()-4)=$C3, MOD((COLUMN()-4)-$C3,$D3)=0)
and used THAT as the conditional statement of the IF() function.
give it a try and see if it works for you.
Paul
-
“Do all the good you can,
By all the means y
userform to add many more rows.
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.
g to create new text boxes: how many?
Could you instead have them already created, and simply set the .Visibility =
false until you hit the button?
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,
A
ntil"
are not EXAMPLES of looping statements, but examples of TYPES of looping
statements.
an EXAMPLE would be something like:
For i = 1 to 10
debug.print cells(i,"A").Value
Next i
i = 0
Do while i < 10
i = i + 1
debug.print i
loop
Paul
-
I'm sorry, but your explanation is confusing.
Can you give 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 all the times you can,
To all the people you can,
As long as eve
set.
Look at:
File->Options->Trust Center
Hit "Trust Center Settings" button on right
Then select Macro settings.
The last item:
"trust access to the VBA project object model"
should be set.
hopefully that takes care of the problem.
Paul
-
End If
Next x
End With
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
ificant.
also, what version of excel are you using?
I noticed the file is in .xls format.
Is that also a requirement?
thank you,
Paul
On Wednesday, June 25, 2014 12:23:34 PM UTC-4, Missy786 wrote:
> Dear all,
>
> I would like to be able to extract certain data from one sheet to an
601 - 700 of 1741 matches
Mail list logo
