20 &
"_" & $E$19 & "_" & $D20,$A$5:$F$804,5,FALSE),0)
take a look and see if you can follow what I've done:
Paul-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places
files.
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
acros work correctly.
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
you ADDING the
data from the Source workbook to the data in the Destination workbook or
REPLACING the data?Will there be duplicate records?What do you do with the
duplicates? Paul-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In
ach f In fldr.Files
If (InStr(1, f.Name, "L1") > 0) Then Workbooks.Open f.path
Next f
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 time
Dim Sht As Worksheet
Application.DisplayAlerts = False
Application.ScreenUpdating = False
For Each Sht In Sheets
If (Sht.Name <> Sheets(1).Name) Then Sht.Delete
Next Sht
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
Paul
-
On Wednesday, April 20, 2016 12:43 AM, Mahesh
wrote:
yes, it is .csv
On Tue, Apr 19, 2016 at 10:18 PM, Devendra Sahay
wrote:
Hi Mahesh,
Please confirm the file format, is it .csv
On Tue, Apr 19, 2016 at 10:16 PM, Mahesh wrote:
Hi,I want to split it by rows.For example-
if
ror Resume Next
Err.Clear
ActiveSheet.Name = cVal
If Err.Number <> 0 Then
Debug.Print cVal.Value & " : " & Err.Description
End If
single workbook (which can then be
moved to separate workbooks)
Can you share a sample (large) of 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
the target value.I also change the
font.
The "current" value is displayed in the status bar.
Once all available combinations are set, a prompt appears.
Right-clicking on one of the values will reset.
Paul-
“Do all the good you can,
By all the means y
te a value that is "2",well, a TEXT value of "2" is not greater than a
NUMERIC value of 1, so the condition is false.but, if you TYPE a 2, then Excel
recognizes that 2 > 1 and the condition is true.
Paul-
“Do all the good you can,
By all
less than 32,767
If you expect more rows than that, use a "Long" data type (2,147,483,647)
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
nd of error are you seeing?
next:You said you have 64 reports to be sent to 64 emails.
You need to figure out how you want to specify the file names and email
addresses and pass that info to the send_mail macro.
Paul-
“Do all the good you can,
By all the m
e & "X" <> "X"))
Then
Str = Replace(Str, Cells(RowNum, "A").Value, Cells(RowNum,
"B").Value)
End If
Next RowNum
fOut.writeline Str
Wend
'
fIN.Close
fOut.Close
MsgBox "finished"
E
th "_1.xml"
the result?
NewXML = "HGSJAM-Encompass_" & Format(Now(), "MMDDYY-HH:MM AM/PM") & "_1.xml"""
Paul-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the pla
no way to guess the hundreds of ways it
might not 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 people you can,
As long as ever you can.” - J
.writeline Str
Wend
'
fIN.Close
fOut.Close
MsgBox "finished"
End Sub Paul-
“Do all the good you can,
By all the means you can,
In all the way
_Distribution.xlsx").Close
End Sub
Sub tOpen2()
ChDir "C:\temp\vba"
Workbooks.Open "Contract_Distribution.xlsx"
Workbooks("Contract_Distribution.xlsx").Close
End Sub
Sub tOpen3()
Dim wb1
ChDir "C:\temp\vba&quo
can you send me a sample file directly?That way I can try out some things
without spending hours building a test file that doesn't look anything at all
like what you have!
You can send it directly to:schreiner_p...@att.net
Paul-
“Do all the good yo
C") >= rngD2 And wshD.Cells(r, "C") <=
rngD3)
Since Column C seems to be dates?
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
If ((FindFlag) And (Trim(wshD.Cells(r, "E").Value) & "X" <> "X")) Then
If (Not (wshD.Cells(r, "E") = rngD4)) Then
FindFlag = False
End If
End If
'
x27;d suggest renaming a file to a .txt extension and try opening it with Excel
and see what you get. 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
EMAILS, put
them in a single email and send them to a specific address, then I have no idea
how to handle 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 a
to do as you wish.
As to your first question:You seem to be doing it.In order to open a userform
when the workbook is opened, place the code in the Workbook_Open event
macro.Which it sounds like you have done!
hope this helps.
Paul-
“Do all the good you can
(4)
= 2fArray(5) = 2x.jpg
So, ye:fArray = Split(Replace(fName,"_","-"), "-")
will work if the filename uses "_", "-", or has a mix of both.
Paul-
“Do all the good you can,
By all the means you can,
In all t
?? You mean like:
fArray = Split(Replace(fName,"_","-"), "-")
??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
& fname)) Then
FileCopy Sourcefld & fname, destfld & fname
End If
Debug.Assert True
End If
Next rng Paul-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the place
counters a #N/A or ERROR value,
then adding the statement:
On Error Resume Next
toward the beginning of the macro should skip the problem values.
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 al
Are you saying that you want the macro to treat the filename like
"atnst-a2-2_m_2_2x.jpg "as if the "-" and the "_" are the same?
In that case, I would temporarily replace the "_" with "-" and split the name
into the array
ct
.body = MsgBody
If ((MsgAttachment & "X" <> "X") _
And (fso.fileexists(MsgAttachment))) Then _
.Attachments.Add MsgAttachment
.Send
End With
Application.ScreenUpdating = True
Application.DisplayAlerts = Tr
s the appropriate
email.
But you have to have Outlook open for it to work.
I can provide that function if it is helpful.
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
accomplish this.But it seems 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 people you can,
As long as ever you can.” - J
loop/row
index if a row is deleted,but I prefer this method.
let me know if you have any questions.
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
itive material.perhaps you can delete or
modify the Excel content before sharing the file with one of these services.
good luck,
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
of the posts, I find that
questions have been resolved by others.
I'll try to be more diligent in the future.
If you're still having issues with this worksheet, let me know.
Paul-
“Do all the good you can,
By all the means you can,
In all the ways
See Comments below.
Paul
On Monday, March 7, 2016 6:45 AM, big smile wrote:
All are requested to take sort out or give solution to my this query please .
Thanks ..
On 6 March 2016 at 17:16, big smile wrote:
| | Hello Friends | | | | |
| | | | | | | |
| | In the
can,
To all the people you can,
As long as ever you can.” - John Wesley
-
On Thursday, March 3, 2016 1:07 PM, big smile wrote:
Respected Mr. Paul
your idea of creating text box is really very nice .. But when i started
using this i found two issues
, this macro will cause some problems for you.
Instead, I inserted a text box, named it "TextBox_Current", and changed the
macro to update this text box.Now, you can insert additional records.
I also changed the event macro to use the GetConcat function to put the summary
value in col
IF(D12<>"",D12,""))
or =IF(C12<>"",C12, IF(D12<>"",D12,""))
It's a little easier to read and follow.but your version works too.
Paul-
“Do all the g
"consolidate" and remove the spaces.
and yes, you can save the file in the same folder. I have the macro ignoring
any .xls files.
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 t
reate another picture and right-click to
select "Change Picture" to use the new picture.
(or delete it and use another method to create your title)
Paul-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places yo
better off adding a "helper"
column and concatenate E and F like:=E2 & "_" & F2 (in column H)then use
:=C2-INDEX($E$2:$G$350,MATCH($A2&"_"&$B2,$H$2:$H$350,0),3)
or put =E2 & "_" & F2 in column D and use:=C2-VLOOKUP(A2&"_
Here.
I made this one exclude anything with "XLS" in the filename.
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
but possible.it that necessary?
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
---
Wait.. you WANT to remove the -M in the Article?
your latest sample includes the -M.
This one should remove 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
Those are not the same filename structure as in your previous sample
filenames,but they match your original request.
This should work for 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
Try this and see how 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 you can,
As long as ever you can.” - John Wesley
Sorry for the delay.I spend 10-12 hours a day throughout the week on a
computer.When a beautiful weekend comes along, I tend to spend my time doing
other things and never even APPROACHED my computer!
Please see my earlier response. Paul-
“Do all the good
There's not near enough information here to make a guess.I'd have to have the
entire workbook and test it myself.
However:You're in VBA Debug.Did you try stepping through one line at a time to
see where it's running into a problem?Do you have any "loops" that are n
sheet,then run the file_list macro (or double-click cell A1).Then
send me the file.I'd be glad to write a simple macro to parse out the file
names.
Paul-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places yo
Easily done, provided you give us some details!
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
I am log into a website using user id & pwd from excel. After login a new
window pops up with software conatining user form with no browser. I need
to click on a button of that user form. I have already tracked down .exe
file name from task bar. How to connect with that form using vba? Is there
not locate file:" & Chr(13) & fMaster
Exit Sub
End If
If (DateDiff("s", dMaster, dUser) <> 0) Then
MsgBox "Not Latest Released Version"
Else
'Call macros here
MsgBox "Running Macros"
End If
uot; function... --I don't work much with pivot
tables. I'd have to experiment with it some.
As for locking:You change the cells properties to "locked" (right-click and
select Format Cells, and the Protection tab)Then, you have to Protect the
worksheet.
Paul
rue" for the function
to return "true".
So... Your test condition is:Length of A1 <= 5ANDthe first two characters are
not "AB"then:add "AB" to the number, formatted as TEXT "0".
You should be able to use:
=IF(AND(LEN(A1)<=5,LEFT(A1,2)<>&quo
1).Value = "utility"
ElseIf rCell.Value = "GAS" Then
Cells(rCell.Row, colLast + 1).Value = "TEST"
End If
'
If Left(Cells(rCell.Row, "G").Value, 4) = "6183&
Thanks Paul and Sky!
I love learning this stuff. GLAD to hear Concatenate was a throwback.
That's a pain to try & remember to type.
That Said, on to the next part. This is the formula I'm using:
=IF((A1)<1,"",(IF(LEN(A1)<=5,(("AB"&(TEXT(A1,&quo
it doesn't have
anything in it!?
I suspect I'm missing something in your 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 all the people you
an be done with the Sumif() function.
See attached.
although, you have values in rows 10 and 11 for product "AAA".Your output seems
to expect a value of 40.But I only see one previous entry of 20.00.
All the other calculations match your expectation, so I'm not sure where the
error
uot;Delete any extra line after the last row with
data."?Excel 2010 allows 1,048,576 rows.You cannot "delete" any of these
rows.You can delete the CONTENTS, but the rows are still there.However, you can
HIDE any or all of these rows.
Can you please elaborate?
Paul
In cell G4, enter formula:=SUMIF(C$4:C4,F4,D$4:D4)and copy the formula down
through column G.
The "$" means that the beginning row of the range is "anchored" which means
that the first row of each range is 4,but the ending row of each range will
increment to match the "current"
row.Paul
This version of Excel has 16,384 columns and 1,048,576 rows.The worksheet
you've provided has those "extraneous" cells hidden.Mr. May has provided the
instructions for displaying those rows.The same technique, but selecting a
column heading will display th
report
generation?
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
but you'll see it in VBA.3) In VBA,
import each of your macro modules and save your VBA project.
Make sure your Subroutines are "public" rather than "private" so that they may
be called from other workbooks.
Paul-
“Do all the good
What is your "issue"?Your subject says "overflow error issue".Where/when does
this occur?
How many rows are in your data?(Integer data types only allow for a maximum
value of 32,768.If your maximum row is larger, then use: dim i as long di
well look like: d--mwhich would mean that
these dates would really be January 1, 2016 and January 2, 2016.
it would really help to see the raw 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
.(it would then save/close the file)
Then, set up a Scheduled Task on a Windows computer that will run daily and
simply open the file at 9:01am.
If you'd like to try this approach, let me know and I can help.
Paul-
“Do all the good you can,
By all the m
If you REALLY want to shorten your formula:
the =concatenate() function is a "throwback" to earlier versions of excel and
only included for "backward compatibility".
"technically", these two are the same
thing:=CONCATENATE("AB",TEXT(I
I added both formulas to the sheet.
Is there a way to get this done using VB, so I can leave the cells blank
and have this occur?
On Wednesday, February 10, 2016 at 4:27:58 PM UTC-8, Paul Bevins wrote:
>
> Hi, I've got an odd problem.
>
> I have a database of some 15000 numbe
Hi Karthik!
Thanks for the reply!
I liked the use of the LEN & AND statements, but your formula seemed longer
than mine.
So I looked at yours again and realized I could ALSO use:
'=IF(Len(I2)<=5,CONCATENATE("AB",TEXT(I2,"0")),I2)
Now granted, it does allow for accidental numbers, like 15,6
; To Sun"
Mon To Sun
Mon To Sun
Mon To Sun
="Mon To " & TEXT(C36,"ddd")
(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
Paul,
OK, there are several things going on here.(and my email is grouping SOME of
them as one conversation, but separating others as if it's a different
discussion)
Some of your statements are confusing.Partially because some terms you're using
have very specific meanings that
sent Value is $10,722.90not $10,700
Attached, I created a simple investment amortization schedule.
See if that 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 times you can,
To
Sorry, my email broke this out as a separate discussion.
It looks like you've had your question answered. good luck!
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 ca
Of COURSE there's a way.There's HUNDREDS of ways.
If you can give some details, and examples, I'm sure we can help with a
solution or two. Paul-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places
is the original number and B1 is this formula, is there a
formula I can insert into C1 that can paste B1 as a number to D1?
It's mostly a security thing. Always get worried I'll forget and delete my
formulas.
Also, if there is a shorter better way to do this, please let me know. I'm
all about the new tricks and better ways.
On Wednesday, February 10, 2016 at 4:52:09 PM UTC-8, Paul Bevins wrote:
>
> Guess, I could add that I've used:
>
> Concatenate ("",A1) to give me all my zeros.
> Right (A2,5) function, to reduce the number to just the f
A automatically?
Also, While I can just copy the column and paste values, I'd PREFER the
numbers to already be numbers and not formulas looking like numbers.
(That's not a biggie, but would be nice.)
On Wednesday, February 10, 2016 at 4:27:58 PM UTC-8, Paul Bevins wrote:
> Hi, I&
nyone have any ideas?
Thanks in advance for any help
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 this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor
Hi, I'm Paul.
Just a normal guy, I like to think of myself as tech savvy & have had a
business at various times supporting both PCs and Microsoft in general.
That said, Not always so good with Excel. Hoping to get some tips.
Paul
On Friday, June 8, 2012 at 12:21:59 PM UTC-7, Ayush J
What do you mean by "scrolling headers"?I don't believe I've ever seen that
term. 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
There MAY be an option in Excel to prompt for this.If so, then it may be
selected when the software is installed, or in a Profile.
If that's the case, it's unlikely we could programmatically change the Excel
settings.
Glad the macro worked! Paul---
workbook would then have to be saved as .xlsm or .xlsb, since the .xlsx
format does not support macros.(that is, if you're using Excel 2007, 2010 or
2013) Paul-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the p
e(Ext) = "xlsx") _
And (Not LCase(Ext) = "docx") _
And (Not LCase(Ext) = "xlsm") _
And (Not LCase(Ext) = "xls") _
And (Not LCase(Ext) = "doc") _
) Then
ActiveSheet.Cells(R, &quo
been declared or assigned.
- In order to continue testing, we need the data files specified in B3 and B4.
Without those, I cannot test further.
Paul-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places yo
ect
ActiveSheet.Paste
'-
' Close New Workbook
'-
NewWB.Close savechanges:=True
'-
thisWB.Activate
Sheets("Data").Select
Selection.AutoFilter
ActiveWorkbook.Save
===Function
Module_Import(ModName)
Dim VBAmodule
Module_Import = False
'--
If (Not fso.folderexists(VBfldr)) Then
MsgBox "Folder not found:" & Chr(13) & VBfldr
Exit Function
End If
If (Not fso.fileexists(VBfldr
t1,it's in the CURRENT workbook, not the new one.you need to:
skip closing the workbook.after filter/copy to clipboard, select the
destination workbook before pasting.
hope this helps.
Paul-
“Do all the good you can,
By all the means you can,
In all the w
file1 and file2 look like.Only to guess wrong and never come close to the
issue you're trying to solve. 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
using macros to generate some reports.
I told him that I'd look into writing a VBA macro in his Excel workbook
(Office 2013)
and try to pull the data from the Google Sheets/Drive programmatically.
Does anyone have any experience using Excel to open and retrieve data from
Google Sheets?
thank
ules.Because I don't think you can
remove the sheet modules without removing the sheet!
If you want to try this approach, let me know and I'll search through my macros
and find the tools you need.
Paul-
“Do all the good you can,
By all the means yo
ActiveWorkbook.Worksheets("Sheet1").Names.Add _
Name:="NamedRange2", _
RefersTo:="=Sheet1!$A$1:$B$10"
Or:
ActiveSheet.Names.Add _
Name:="NamedRange2", _
RefersTo:="=Sheet1!$A$1:$B$10"
Paul-
“Do
Do you mean:
Application.Dialogs(xlDialogCustomViews).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 people you can,
As long as ever you can.” - John
OOps..I copied one of my "quick and dirty" macrosand change the variable names
to be somewhat more intuitive.
But I guess I didn't change them all!
sorry about that. Paul-
“Do all the good you can,
By all the means you can,
In all the ways
I have a excel file containing the Roll numbers of candidates (attached
file).
To find their result from the website, I need to follow the below link
http://www.tripurauniv.in/index.php/result > TDP/TDPH (B.A, B.Sc, B.Com)
2nd Semester Examination -2015>Search Result
Then I put the roll no
t past this is to write a VBA macro that loads the
data into an arrayand processes the array.
But before I try something like that:
Is this the way the data is "presented" to you?is there any "unique" identifier
for the records?
Paul-
“Do
Are you looking for a VBA solution to move files from one folder to another?Are
you wanting to simply count the files in a folder?
What is it you're trying to accomplish?
for simply cycling through files you could use:
Set fso = CreateObject("Scripting.FileSystemObject")
fPath = "C:\temp"
has"I
have no idea what that means.does that mean that if there are not sufficient
records for that time period, you want to take an entry from another?
If you could:Take the records you've provided for Januaryand show me what you
would EXPECT the (4) ag
have a "\" at the
end)then this macro should work for 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 the people
aOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Data Sheet").Sort
.SetRange Range("DA1:DB65000")
Paul-
“Do all the good you can,
By all the means you can,
In all the
201 - 300 of 1741 matches
Mail list logo
