current row.You can then copy this function to
the rows in column C and D.
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.
en each column,then you're going
to need a VBA macro to accomplish this.
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
ot;Table"
in: Dim myTable As Table and select "Definition".this will tell you where the
keyword is defined.
Perhaps the other application has an additional "Reference" included.
Paul-
“Do all the good you can,
By all the means you can
You can create a Worksheet_Change event.check to see if the cell changed
(Target) is the cell with your pull-down selection, then process accordingly:
Private Sub Worksheet_Change(ByVal Target As Range)
If (Target.Address = "$A$4") Then
Debug.Assert False
End If
En
= "Distinction"
Case 81 To 90: rng.Offset(0, 1).Value = "Very good"
Case 71 To 80: rng.Offset(0, 1).Value = "Good"
Case 61 To 70: rng.Offset(0, 1).Value = "Alright"
Case Else: rng.Offset(0, 1)
).Value
Next nRow
Next SetNo
End Sub
If that's not what you meant, then I need further explanation.
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 al
The "escape" character in Excel filters is a tilde (~)
so, in your filter, you could look for records that begin 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 tim
ot;Invoice").Range("E34").ValueWorksheets("invoice
tracker").ActivateRange("A300").End(xlUp).Offset(1, 0).Select
ActiveCell.Value = InvoiceID
ActiveCell.Offset(0, 1).Value = InvoiceDate
ActiveCell.Offset(0, 2).Value = Client
ActiveCell.Offset(0, 3).Value = subTotal
Act
pare it to the output (columns I:L) and "guess" what criteria was
used to get the result.
On top of that, you have the fields being randomly generated!
so, I cannot tell what the original input data was to even BEGIN to guess what
criteria is used to determine output.
I'm
mation from the first list and creating the
second?What do you do with duplicates? (two or three employees in the same
department)?
If you were doing it manually, what steps would you perform?
Paul-
“Do all the good you can,
By all the means you can,
In a
I'm sorry.I'm not sure what you're trying to do!
Your two attachments have a single column of data.They have (7) values in
common, but I'm not sure what you want to do with them.What macro did you
modify?
Paul-
“Do all the good you c
except the one called "Master" and then
copies the data into new sheets (creating them as required).
hope this points you in the right direction.
Paul-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the pl
u do with it?
I have the two files you sent originally, but I don't see what information is
common between the two files,so I don't know how you get from the "Index-Match"
file to the "output" file. Paul-
“Do all the good you c
urces.
this kind of thing can be easily done with VBA.
I can help with this if you'd like.I've done it before with nearly 300
workbooks.
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
can
rearrange the output columns in any order and the report automatically writes
to the appropriate column.
but I have no way of determining what you want unless you can describe it in a
way I can understand. Paul-
“Do all the good you can,
By all the
Take a look at the macros attached.See if it looks like what you envisioned, or
at least gives you a starting point.
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
First occurrence of... what?First occurrence of the Order ID?customer ID?
Is the data already sorted in the order by which you want to determine "first
occurrence"?
Paul-
“Do all the good you can,
By all the means you can,
In all the ways you c
ish to record in the Workbook2.xlsx 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 you
let me know which comes closer to your intent.
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
-
Debug.Print zDate & " : " & zFile
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 can.” - J
Sheets(1).UsedRange
If (Rng.Interior.Color = 49407) Then
nRow = nRow + 1
Sheets("Sheet1").Cells(nRow, "A").Value = Rng.Value
End If
Next Rng
End Sub
I don't know that it can be done "at the same time" within Excel.
Paul---
folders, named "January 2016" through "June 2016".In
each folder, I copied your sample file 30 times (so I had 180 copies)
I then made reports for all 6 folders.
It took about 90 seconds.
hope this gives you enough to work with.
Paul-
“D
n & " mins "
msg = msg & TSec & " sec"
MsgBox msg
'xxx
End Sub
Paul-
“Do all the good you can,
B
write the event
information to Excel.
Any idea how to approach this with VBA?
(Yes, I've Googled it and have not had any luck)
thank you for your attention.
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
he file in folder BB
should appear... where? on the same sheet?
Can you send me a couple of samples of what the files look like and what you
want the summary sheet to look like?
Paul-
“Do all the good you can,
By all the means you can,
In all the ways you can
Hi everyone,
I have one table with 2 columns:
X values and Y values
But I have something like 20 or 30 rows.
i need to find the approximative value Y for a given X but my input X is
always between two values of X in my table
For example:
X - Y
1 - 10
2 - 20
3 - 30
For X=2.5, Y = ?
This is a
I need the same type of formula for adding the values of the 15 columns in
the subsets.
And again the same type of formula for adding all 60 columns and getting a
value for that.
A little complicated, I know, but really, it's only one formula, three
different ways.
Paul
--
Are you =EX
imply add a call to HideAllSheets to this macro before the Msgbox line.
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
Hi Experts,
Can we create a pivot based on another pivot?(*Interview Question*)
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 @
https://www.facebook.com/discussexcel
FORUM RULES
Your buttons call two separate macros.Print_OptionandSUPPRESS_ROWS
to combine the two, simply have the Print_Option macro "call" the SUPPRESS_ROWS
macro, like:
Sub Print_option()
SUPRESS_ROWS
Application.Dialogs(xlDialogPrint).Show
End Sub
Paul---
Could I get a copy of the sheet?What do you base "current" year on, the year of
"today's" date?(rather than a fiscal year or other criteria)
There are several options.But I'd rather not try to recreate your data, only to
find out my creat
For Each wsSheet In ActiveWorkbook.Worksheets
If wsSheet.Name <> "read" Then
wsSheet.Visible = xlSheetHidden
End If
Next wsSheet
End Sub
Paul-
“Do all the good you can,
By all the means you can,
In all the ways you
There's nothing that is "automatic".
Attached uses a Workbook_open event macro to hide the sheets when you OPEN the
document.
That way, it doesn't matter whether the sheets are visible or not when the file
is saved, but it will hide the sheets whe
ts are always hidden before saving the file)
Note: Using a macro, you can use .Visible = xlVeryHiddenwhich will hide the
sheets and they will not show up when trying to manually unhide the sheets!
Paul-
“Do all the good you can,
By all the means you can,
In
omeone to bypass the protection on copyrighted material)
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
What do you mean by "not working properly"?
I opened your file and compared the values to the expected values and it seems
to be working as expected. Paul-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the
You're not defining a delimiter.Are you using a comma (,) as the delimiter?
then you need Comma:=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 you can,
To all the p
your logic.Starting with column 1,
NOthing should happen with column 1.
Perhaps you need to start with the last column and work backwards and stop when
you encounter a column with values.
Paul-
“Do all the good you can,
By all the means you can,
In all the ways yo
Rng
End Function
To use it, you could use:=vacation($B3:$H3,TRUE) for the first "WO"
and=vacation($B3:$H3,FALSE) for the last
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 ti
Yes:
First, make sure the functions are defined as "public".As in:In
Book1.xlsb:Public Function Test_Calc(iVal)Test_Calc = iVal + 10End Function
In Book2, use:=Book1.xlsb!Test_Calc(A2)
Paul-
“Do all the good you can,
By all the means you c
You can't have an Excel function replace the values in the cell.You could use
VBA, but it would be easier to simply use VLOOKUP.
In Cell B21, put:=VLOOKUP(C21,$A$10:$B$17,2,FALSE)Then copy this down to row 29.
HIghlight the cells and ctrl-CThen, right-click cell C21 and Paste Values.
can execute the "initialize" event to test,or you can create a macro that
contains: Form_Ranges.show
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
That's what I meant by:
You can modify it to also include changing the background highlighting as well.
Do you have the macro that changes the color and resets it?Simply add those
statements in the appropriate place in the macro.
Paul-
“Do all the goo
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
-
On
same row and updates column V.
Can you send me your file so that I don't have to re-create the file in order
to write and test it?(or at least one sheet)
Paul-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the place
.PatternTintAndShade = 0
End With
Rng.FormulaR1C1 = "Passed"
End If
End If
Next Rng
On Friday, November 4, 2016 4:18 AM, Nadal Mir wrote:
Hi paul these is two code u gave to me. I try to combine it but come out with
error. Please help
See answers below:
On Friday, November 4, 2016 2:00 AM, amar takale
wrote:
Dear Paul Sir,
This is fabulous work!
I think this must be flashing on the front page! :D. And I am Extremely sorry
for so late reply bcos of diwali vacation. I am VBA learner so only basic
knowledge of VBA
hen
With Rng.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Rng.FormulaR1C1 = "Passed"
End If
End If
Paul-
“Do all the good
The problem is that these aren't "cell values" or even data validation using a
list.They are Form Control objects.
I don't know how to get the value of form controls without using VBA.I could
write a macro that loops through all the objects on a sheet and record
I set a different Print Area on multiple sheets and
used:ActiveSheet.ExportAsFixedFormat to create the pdf and it worked exactly as
I expected.(multi-page PDF with different areas printed)
Paul-
“Do all the good you can,
By all the means you can,
In all
simply need to change the offset value to 3 instead of
2:dCell.Column + 3).Value
--The file you sent me has 82 entries for Part Number 3716-000275, with
Qty 2 each (for qty=164) at 163.4 each.There are NO Records with a 0 price.
I fixed the offset so t
If multiple words are found, then the macro loops through records and updates
the "Flag"column (E) if all words are found IN COLUMN D!Then filters the data
on column "E".
Take a look and see if it works the way you envisioned.
If not, let me know.
also, let me know if you need he
0")
If (rng.Value = "Failed") Then
With rng.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
rng.FormulaR1C1 = "Passed"
End If
Next rng
End Sub
Paul-
I wrote a macro to report the data.
Take a look at the attached file and see if you can follow along.
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
"Part Number", then this will
produce an error.
When I created a sample with "Part Number" in the heading,the code worked
properly.
What is yours doing?
Paul-
“Do all the good you can,
By all the means you can,
In all the ways you ca
pient & ";" & "g...@yahoo.com"
Which, if you place a "watch" on the variable, will result in:
Recipient = "a...@yahoo.com;def2...@yahoo.com;g...@yahoo.com"
and should work correctly.
Paul-
“Do all the good you
n you can.
That is: Cells(1,"C") for row 1, column C instead of cells(1,3)You're less
likely to confuse the row/column.
Of course, this doesn't apply when you're referring to the array index.
Paul-
“Do all the good you can,
By all the mean
step through it and figure out what
happens in the second iteration.
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
Is it OK to email you directly?(you can reply to: schreiner_paul @ att.net)
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
;ll notice the Delete loop starts at the
end and moves UP
For nRow = nRows To 2 Step -1
You'll want to do the same when you delete from your first 200 records.
does that make sense?
Paul-
“Do all the good you can,
By all the means you can,
In all the wa
ues and initialize the array
with blanks. Then break out of the above loop when the array value is blank.
I like using the Dictionary Object. It eliminates the need for looping through
an array, checking values, and you don't have to anticipate the maximum number
t;A:A"))
For nRow = 2 To nRows
If (Not Dict_Type.exists(ActiveSheet.Cells(nRow, "A").Value)) Then
Dict_Type.Add ActiveSheet.Cells(nRow, "A").Value, nRow
End If
Next nRow
Form_Records.Lst_Type.Clear
tArray = Dict_Type.keys
Anything is POSSIBLE.It depends on your data to know how difficult it would be.
Can you provide a sample 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
What is the format of the data source?What are you trying to do with 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
If (ActiveSheet.AutoFilter.Filters(nFilter).On) Then
If (msg2 <> "") Then msg2 = msg2 & ", "
aCell = Split(Cells(1, nFilter).Address, "$")
msg2 = msg2 & aCell(1)
t so that you
only run this if you change something in the range("A2:A6")Otherwise, it checks
the entire range even when you change something elsewhere on the sheet.
Paul-
“Do all the good you can,
By all the means you can,
In all t
ed Excel to it
previously?Do you know anything about SQL or any other type of programming?
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
not to say that someone hasn't found a way, but I actually hope it isn't
easy. 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,
e interested in this approach, I could throw together an Event 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 times you can,
To all the people you can,
As long as ever you
sheet is protected.
does this fix your 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 eve
This should be a simple =sumif() function:
in Cell B2, use:=SUMIF(Sheet2!A:A,A2,Sheet2!B:B)
and copy down. 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
put the IF() test right after the For i = 1 To lrline so
that it skips the other tests if the date doesn't match.
Could you send me a copy of your file so that I can try to follow what the
macro does?
Paul-
“Do all the good you can,
By
In this case, DATAPAGE is a named range in your workbook.
You can try to find it by looking in the pull-down above the column headings:
or look in our Name Manager on the Formulas tab:
Either should show you where this ranged is defined.
Paul-
“Do all the
| 500 |
Then TL2 and TL4 would receive "Incentive".
I've included formulas for both scenarios.
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
In this case, in cell C5 you could use a calculation like:=(Q35 -1) * 10
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
activate event to check to see what cells
do not match the Public variables and report the changes.
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 yo
(B21,A2:A21)-2)E21: =INDEX(A2:A21,MATCH(B21,A2:A21)+1) F21:
=INDEX(A2:A21,MATCH(B21,A2:A21)+2)
If you do NOT want to sort your data, I'm not sure you can do it without
writing a VBA macro.(which would load the data into an array, sort it, then use
VBA to do what the above formulae accomplish)
ntil you recalculate.
In the attached, I have set Calculation to "Automatic".
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
You're looking for a function like:=IFERROR(VLOOKUP(F2,D:D,1,FALSE),"No Record
Found") 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
A").Value, 4, 2) _
& "/" & Right(ActiveSheet.Cells(nRow, "A").Value, 4))
End If
Next nRow
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 c
Can you give me a couple sample files describing what you want?
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
n you need to use string functions to convert the
text value to a date value.
Like:
=date(right(A2,4),mid(A2,4,2),left(A2,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
t; which is "Employee Id"But
those are numeric, while column E is names.
Please give examples of what you want it to look like before and after.
Paul-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places
ix of sheets and total them
individually.
Like the attached.You only need to add the sheet names to row 2 and copy the
formulae in rows 3 and 4.If you need a total, then simply add them.
If I've totally missed your point, I'm sorry.
Paul-
“Do all
nge("A1:F8")
CellVal.Value = CellVal.Value * ValIncr
Next CellVal
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,
o "convert" it to a number, use the int() function, like:
=int(Text(A1,"mmdd"))
Is this 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 pla
The line that actually does the printing is:
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:=tprinter,
Collate:=True, _ IgnorePrintAreas:=False
comment it out and you should be good to go!
Paul-
“Do all the good you can,
By all
The tPrint function is what does the actual printing.you could simply comment
out the line that calls this function:
' ** added section end **ttdate1 = Mid(ttdate, 4, 2) + "/" +
Left(ttdate, 2) + "/" + Right(ttdate, 2)'TPrint ttref, t
To compare two dates, use the DateDiff() function.
Something like:Sub ChkSort()
Dim nRow
For nRow = 1 To 10
Select Case DateDiff("d", Cells(nRow, "A").Value, Cells(nRow + 1,
"A").Value)
Case Is > 0
MsgBox "Sort is Ascending"
Exit For
the entire workbook) are located in
the ThisWorkBook module.
In your file, your Workbook Event macro is located in a "standard" module.
Located here, no "event" takes place to trigger the macro.
You need to move it to the ThisWorkbook module.
Paul--
Please note the Forum rules:
5) Jobs posting is not allowed. 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
Please note the rules for this forum(specifically #5)
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
ged to a
non-zero value,about the only way to do so is to create a VBA Change Event.In
that event, you can test to see if the changed cell is the target cell
(A1),then test to see if the date cell already has a value.If the changed cell
is A1 and the date is blank, then put in the
Have you checked out the Forum Rules?Especially #5 ?
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
bly malicious
macros, you could compare file sizes before/after the file is saved and report
differences for future investigation.
If you want to pursue that route, I could put something together.
Paul-
“Do all the good you can,
By all the means you can,
In
e password to UNprotect it.
I experienced the same issue you did with regard to opening the file.I
protected the WORKBOOK (with password), then unprotected it (with password)and
the problem went away.
Paul-
“Do all the good you can,
By all the means you c
All of your values can be extracted with string functions like:LEFT(A3,6)
MID(A3,7,6)
DATEVALUE(MID(A3,13,11))
MID(A3,24,2)
MID(A3,26,20)
But if future values are not in the same format, these functions may not work.
Paul-
“Do all the good you can,
By all
updated pricing)Then,
the Output sheet is copied to a new workbook and saved as the output file.
This file can then be emailed to the customers (either manually, or with a
macro)
If you can give me a sample file to work with, I'd be glad to put something
together.
Paul
"D") > 0) Then
DestinationFile = wb.Name
ElseIf (InStr(1, wb.Name, "M") > 0) Then
SourceFile = wb.Name
End If
Next wb
MsgBox "DestinationFile: " & SourceFile & Chr(13) & "DestinationFile: " &
SourceFile
End Sub
P
e dictionaries, then use these Dictionary objects to
generate a summary report.
The summary report takes about 90 seconds to process all 160,000 records.
Paul-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the pla
101 - 200 of 1741 matches
Mail list logo
