$$Excel-Macros$$ Buy Counterfeit Money Online, Buy Degree Diploma Online, Buy Driver's License Online @ Cheap Price, Buy Id Cards Online. etc. - AllDocsCenter.com

[Andy marley]

we are specialized in the making of genuine passport SSN, ID card, Birth 
Certificate, Visa, PR, Diplomas and many other documents of very unique 
quality. We have produced passport, Drivers license, SSN, ID card, Birth 
Certificate, Diploma and other documents

Contact Information:

Name: AllDocsCenter
Mobile No.: +1 336 864 0385s
Email:in...@alldocscenter.com 
Website: https://alldocscenter.com

buy driver's license online
buy real visa online
buy real registered drivers license
buy registered drivers license online
buy real drivers license online
buy certificates online
fake international driving license
purchase drivers license online
buy id card online with shipping and delivery
Buy Passport Online
buy real registered passport online
buy a real registered usa passport
buy real u.s.a passport online
buy fake uae passport online

https://alldocscenter.com/
https://alldocscenter.com/counterfeit-money/
https://alldocscenter.com/get-a-passport/
https://alldocscenter.com/get-a-drivers-license/
https://alldocscenter.com/product/buy-counterfeit-australian-dollar-bills/
https://alldocscenter.com/product/buy-fake-british-pounds-online/
https://alldocscenter.com/product/buy-fake-euro-banknotes-online/
https://alldocscenter.com/product/where-to-buy-undetected-canadian-dollars/
https://alldocscenter.com/product/where-to-buy-fake-swiss-franc-cny/
https://alldocscenter.com/get-a-drivers-license/
https://alldocscenter.com/get-a-passport/
https://alldocscenter.com/visa/
https://alldocscenter.com/cae/


Contact Information:

Name: AllDocsCenter
Mobile No.: +1 336 864 0385
Email:in...@alldocscenter.com 
Website: https://alldocscenter.com

-- 
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 thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.

NOTE  : Don't ever post confidential data in a workbook. Forum owners and 
members are not responsible for any loss.
--- 
You received this message because you are subscribed to the Google Groups "MS 
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to excel-macros+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/excel-macros/2a8c5a87-509d-42b2-8694-9da0ba7a4a22n%40googlegroups.com.

$$Excel-Macros$$ Re: Excel Add-in (that enables Auto-complete)

[Andy]

Hi Abhi,

I have been searching this for years. Thanks a lot for the code!
However, when I type a word which is not on the item list and press OK, it 
will show run-time error '381': Could not get the List property. Invalid 
property array index.
I would like to show the text that I type if it cannot be found on the list.

Regards,
Andy

-- 
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 thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.

NOTE  : Don't ever post confidential data in a workbook. Forum owners and 
members are not responsible for any loss.
--- 
You received this message because you are subscribed to the Google Groups MS 
EXCEL AND VBA MACROS group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to 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.

$$Excel-Macros$$ Manipulating data in rows into Columns

[Andy McGooner]

Hi All,

I have a spreadsheet that contains some test results that go across
multiple columns on each row. Now these results are unique to a serial
number, however can be tested on multiple dates i.e. after software
updates etc. Therefore need to be on seperate rows. See example below:

  Ser No   DateCol 1Col 2 Col 3Col 4
Col 5
  01   11/01/11 16.5  9.510
1112
  02   12/01/11 14.5  7.511
11
  03   15/01/11 12.5  7.5
1112
  01   21/02/11 16.5  9.5
1112

The problem here is twofold in that i need to manipulate the data into
another sheet into 3 columns with each result having its own row but
retaining its original date and serial number. There is also the
problem that there may be blanks in some of the columns associated
with the ser no as shown above. Anyway can anyone help me get my data
from the condition shown above into the condition below:

Ser NoDate   Result
  01  11/01/11 16.5
  01  11/01/11  9.5
  01  11/01/11 10
  01  11/01/11 11
  01  11/01/11 12

And so on

Anyway any help would be greatly appreciated

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com


Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

Re: $$Excel-Macros$$ Problem extracting data from multiple columns

[Andy McGooner]

Why cant i attach to this

On Sep 5, 3:04 pm, dguillett1 dguille...@gmail.com wrote:
 Post a file



 -Original Message-
 From: Andy McGooner
 Sent: Monday, September 05, 2011 7:56 AM
 To: MS EXCEL AND VBA MACROS

 Cc: amcni...@jaguarlandrover.com
 Subject: $$Excel-Macros$$ Problemextractingdatafrom multiple columns

 Hi All,

 I have a spreadsheet that can have up to 5datacolumn entries per row
 asscociated to a date etc. The problem is there is multiple rows and i
 really want to move it all into 2 columns and keep the date associated
 with thedatai am moving as i intend to put these in a graph.

 I could do this manually but there will always bedatagetting added,
 so would like to be able to do this with a macro. I have tried using
 autofilters, offsets, Xlup etc but ii keepcant get it to work, i
 assume because there is notdatain every cellof each column

 Anyway idoes anyone know how i can make it go across each row in the
 sheet extract thedataand add it into a new row on another sheet
 whilst retaining the date associated with it.

 Apologies if this does not make sense, however can send an extract
 from the main workbook if it helps.

 Thanks in Advance,

 Andy

 --
 ---­---
 Some important links for excel users:
 1. Follow us on TWITTER for tips tricks and links 
 :http://twitter.com/exceldailytip
 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310
 3. Excel tutorials athttp://www.excel-macros.blogspot.com
 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com

 To post to this group, send email to excel-macros@googlegroups.com

 
 Like our page on facebook , Just follow below 
 linkhttp://www.facebook.com/discussexcel- Hide quoted text -

 - Show quoted text -

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com


Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

$$Excel-Macros$$ Problem extracting data from multiple columns

[Andy McGooner]

Hi All,

I have a spreadsheet that can have up to 5 data column entries per row
asscociated to a date etc. The problem is there is multiple rows and i
really want to move it all into 2 columns and keep the date associated
with the data i am moving as i intend to put these in a graph.

I could do this manually but there will always be data getting added,
so would like to be able to do this with a macro. I have tried using
autofilters, offsets, Xlup etc but ii keepcant get it to work, i
assume because there is not data in every cellof each column

Anyway idoes anyone know how i can make it go across each row in the
sheet extract the data and add it into a new row on another sheet
whilst retaining the date associated with it.


Apologies if this does not make sense, however can send an extract
from the main workbook if it helps.

Thanks in Advance,

Andy

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com


Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

$$Excel-Macros$$ Run-time error 1004 when no file selected

[Andy McGooner]

Hi All,

I have a spreadsheet that stores data from a text file, the idea being
that it opens the file dialog and i select the relevant text file. The
problem is that when i do the file open and press cancel i get the
folowing message:

Run-time error '1004'

false.xls could not be found

How do i do it so that if cancel is selected it will exit the sub
routine.

Code is as follows:

Workbooks.OpenText Filename:= _
UserFilename _
, Origin:=xlMSDOS, StartRow:=1, DataType:=xlFixedWidth,
FieldInfo:= _
Array(0, 2), TrailingMinusNumbers:=True

Thanks in Advance

Andy

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com


Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

$$Excel-Macros$$ Date confusion when copying from CSV to Excel

[Andy]

Hey there.

This problem is giving me a major headache and no post I have read can
seem to help with the problem I am having.

I have some code to copy any present data from CSV files to a main
workbook (basebook) but some seem to paste over as mm/dd/yy instead of
dd/mm/yy...

Two of the CSV's that import differently have the following formats
when I open them through Excel:
06/10/2010  07:00:00 (custom - dd/mm/ hh:mm) - copies to Excel as
10/06/2010  07:00:00
31/08/2010  13:00:00 (custom - dd/mm/ hh:mm) - copies to Excel as
31/08/2010  13:00:00

So the first is obviously wrong and I can change the formatting in
Excel, just not to what I want as that would involve swapping the day
with the month...
The second is as I want it and the format cannot be changed in Excel,
which is fine.

I have read that it may be down to regional settings but I am unable
to give a fix for this as the CSV files come in from different users
and we have a load of them that cannot be redone.

I have tried changing the code to paste as values only and various
other fixes but am so far unsuccessful. I have also read that I could
convert the CSV's to .txt but I have no experience with this through
code and would rather not if avoidable.

The code which copies the data is below. Any help would be really
appreciated. I even started to consider if there is a way to just swap
the day with the month through VBA as the ones that are correct seem
to be stuck that way anyway but I have no idea how to accomplish it...

 'Define the SourceRange
With mybook.Worksheets(1)
LastRow = .Cells(Rows.Count, A).End(xlUp).Row
'Now you know where the list (to be copied) ends

Set sourceRange = mybook.Worksheets(1).Range(A2, A 
LastRow).EntireRow 'Sets the range to copy.

End With


'Define where to put the source values
With basebook.Worksheets(Summary)
'Establish the last used row in the target ws
LastRow = .Cells(Rows.Count, A).End(xlUp).Row
'Copy the whole lot over starting from row that's =lastRow + 1

sourceRange.Copy
basebook.Worksheets(Summary).Cells(LastRow + 1,
A).PasteSpecial (xlPasteValues)

'basebook.Worksheets(Summary).Columns(B:C).NumberFormat =
mm/dd/ hh:mm:ss

End With

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com


Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts

$$Excel-Macros$$ VBA - Alternative to .Lookin

[Andy]

Hi all,

I managed to get some code (almost) working, as below.

It uses FileSearch to find CSV files saved within Outlook and imports
them to the current Excel Workbook.

It worked fine the first time (although for some reason didn't find
and import every single file) but after that the .lookin path reset
itself to my Personal drive and refused to accept the path I gave to
it, despite being the same as my first attempt.

I have read many occasions where .Lookin is flawed and there are
better alternatives but i am unable to work it into my code
efficiently...

Any help would be appreciated - thanks!


Sub ImportAirport()

Dim objOL As Outlook.Application
Dim objMsg As Outlook.DocumentItem 'Object
Dim objAttachments As Outlook.Attachments
Dim basebook As Workbook
Dim mybook As Workbook
Dim sourceRange As Range
Dim destrange As Range
Dim rnum As Long
Dim i As Long
Dim a As Long
Dim LastRow As Long

Application.DisplayAlerts = False
Application.ScreenUpdating = False

Dim oOL As Outlook.Application
Set oOL = GetObject(, Outlook.Application)

If (oOL Is Nothing) Then
Set oOL = CreateObject(Outlook.Application)
End If
Dim oFolder As Outlook.MAPIFolder

Set oFolder = Outlook.Session.PickFolder
With Application.FileSearch
.NewSearch
.LookIn = oFolder
.Filename = *Airport*.csv
.MatchTextExactly = False
.FileType = msoFileTypeExcelWorkbooks
If .Execute()  0 Then
Set basebook = ThisWorkbook
rnum = 2
For i = 1 To .FoundFiles.Count

Set mybook = Workbooks.Open(.FoundFiles(i))
   Application.AskToUpdateLinks = False
'Define the SourceRange
With mybook.Worksheets(1)
LastRow = .Cells(Rows.Count, A).End(xlUp).Row
'Now you know where the list (to be copied) ends
Set sourceRange = .Range(A2, A  LastRow).EntireRow
'Sets the range to copy.

End With
'Define where to put the source values
With basebook.Worksheets(Airport)
'Establish the last used row in the target ws
LastRow = .Cells(Rows.Count, A).End(xlUp).Row
'Copy the whole lot over starting from row that's =lastRow + 1
sourceRange.Copy Destination:=.Cells(LastRow + 1, A)

 End With

mybook.Close SaveChanges:=False
rnum = i * a + 1
Next i
End If
End With
Call ImportExams
Call ImportIntlTrade
Call ImportMail
Call ImportMaritime
Call ImportRail
Call ImportScanning
Call ImportSummary

Call DeleteUnused

Set objAttachments = Nothing
Set objMsg = Nothing
Set objOL = Nothing

Exit Sub

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com


Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts

$$Excel-Macros$$ Verifying Text Box input from excel spreadsheet prior to continuing

[Andy McGooner]

Hi All,

Apologies but i am completely stumped on this one and all searches of
the forums etc have turned up nothing. Anyway I am putting together a
tracker that has a user interface to allow then to stick in some text,
which will automatically find the corresponding values from a base
data sheet and hence update an actual tracking worksheet.

However as i dont want to lose any new base data, then i want to check
that the info inserted is already in the base data sheet and if not
force them to add it so that it is there next time round. Now i was
thinking off using something along the same lines as a VLookUp or find
etc, however cannot find any help on how to actually use this to read
the contents from a text box (in my user form) and return a yes/no,
True/False etc that the base data sheet conatins the information
already.

Any help would be greatly appreciated.

Andy

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com


HELP US GROW !!

We reach over 6,800 subscribers worldwide and receive many nice notes about the 
learning and support from the group.Let friends and co-workers know they can 
subscribe to group at http://groups.google.com/group/excel-macros/subscribe

$$Excel-Macros$$ Validating multiple sheets with VBA

[Andy]

Heya,

It's as simple as the subject states but I can't seem to find an
answer anywhere.

My code at the moment is:

Worksheets(Array(Sat EEA, Sun EEA, Mon EEA, Tue EEA, Wed
EEA, Thu EEA, _
Fri EEA, Sat NonEEA, Sun NonEEA, Mon NonEEA, Tue
NonEEA, Wed NonEEA, _
Thu NonEEA, Fri NonEEA)).Select

'Column C Validation
With Range(C9).Validation
.Delete
.Add Type:=xlValidateTime, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween, Formula1:=12:00:00 AM, Formula2:=12:59:00 AM
.ErrorTitle = Invalid Entry
.InputMessage = Enter a time between 00:00 (12 AM) and 00:59
.ErrorMessage = _
This row is for measurements commencing between 00:00 (12 AM)
and 00:59 only.
End With

With Range(C10).Validation
.Delete
.Add Type:=xlValidateTime, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween, Formula1:=01:00:00 AM, Formula2:=01:59:00 AM
.ErrorTitle = Invalid Entry
.InputMessage = Enter a time between 01:00 and 01:59
.ErrorMessage = _
This row is for measurements commencing between 01:00 and
01:59 only.
End With

With Range(C11).Validation
.Delete
.Add Type:=xlValidateTime, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween, Formula1:=02:00:00 AM, Formula2:=02:59:00 AM
.ErrorTitle = Invalid Entry
.InputMessage = Enter a time between 02:00 and 02:59
.ErrorMessage = _
This row is for measurements commencing between 02:00 and
02:59 only.
End With

'Continues for ~20 other cells.

End sub

Validation doesn't appear to work when sheets are selected as an array
and I'd rather not copy and paste the 100+ lines of code for each
sheet in the workbook. I cannot use For Each Worksheet as a few sheets
do not need the validation.

Any ideas?

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com


HELP US GROW !!

We reach over 6,800 subscribers worldwide and receive many nice notes about the 
learning and support from the group.Let friends and co-workers know they can 
subscribe to group at http://groups.google.com/group/excel-macros/subscribe

Re: $$Excel-Macros$$ VBA VLookup dates

[Andy]

Dave,

Apologies for the late response.

Very much appreciate the effort you have put into this - it works
perfectly!

Thanks again!

Andy.

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com


HELP US GROW !!

We reach over 6,800 subscribers worldwide and receive many nice notes about the 
learning and support from the group.Let friends and co-workers know they can 
subscribe to group at http://groups.google.com/group/excel-macros/subscribe

To unsubscribe, reply using remove me as the subject.

Re: $$Excel-Macros$$ VBA VLookup dates

[Andy]

Dave,

Appreciate the effort - thanks a lot.

I started to think of another way around the problem but that's
stumping me as well...

Sub Test()
Dim lookFor As Range
Dim rng As Range
Dim col As Integer
Dim found As Variant

If Range(A2) =  Then
Range(H2) = 

Else:
Set lookFor = Sheets(Access Data).Range(G2)
Set rng = Sheets(Front page).Range(fortnight)
col = 3

found = Application.vlookup(lookFor.Value, rng, col, 0)

Range(H2).Value = found

Range(A2).Offset(1, 0).Select

Do While ActiveCell.Value  Empty

Set lookFor = Sheets(Access Data).ActiveCell.Offset(0, 6)
Set rng = Sheets(Front page).Range(fortnight)
col = 3

found = Application.vlookup(lookFor.Value, rng, col, 0)

ActiveCell.Offset(0, 1).Value = found

ActiveCell.Offset(1, -7).Select

Loop

End If
End Sub

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com


HELP US GROW !!

We reach over 6,800 subscribers worldwide and receive many nice notes about the 
learning and support from the group.Let friends and co-workers know they can 
subscribe to group at http://groups.google.com/group/excel-macros/subscribe

To unsubscribe from this group, send email to 
excel-macros+unsubscribegooglegroups.com or reply to this email with the words 
REMOVE ME as the subject.

Re: $$Excel-Macros$$ VBA VLookup dates

[Andy]

Managed to get a bit further. The Lookup part is working perfectly but
I can't figure out how to turn the #N/A entries into blanks...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lookFor As Range
Dim rng As Range
Dim col As Integer
Dim found As Variant

Set lookFor = Sheets(Access Data).Range(G2:G5)
Set rng = Sheets(Front page).Range(fortnight)
col = 3

found = Application.vlookup(lookFor.Value, rng, col, 0)

Range(H2:H5).Value = found

End Sub

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com


HELP US GROW !!

We reach over 6,800 subscribers worldwide and receive many nice notes about the 
learning and support from the group.Let friends and co-workers know they can 
subscribe to group at http://groups.google.com/group/excel-macros/subscribe

To unsubscribe from this group, send email to 
excel-macros+unsubscribegooglegroups.com or reply to this email with the words 
REMOVE ME as the subject.

Re: $$Excel-Macros$$ VBA VLookup dates

[Andy]

Sorry for the extra messages but I decided to delete most of the data
in a workbook copy so that I could upload it...

http://excel-macros.googlegroups.com/web/2010%2003%2023%20TEST.xls?hl=engsc=c-8xyhYAAAB4suMG83bGpxYXyXRb7VtTTCBlxQqdFNTZwwfBwm5u_Q

Basically I want the code to stop calculating once it hits an error
(#N/A) or at least to show blanks instead.

If there is a better way to do this I'm all ears as a lot of data is
usually entered into these sheets.

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com


HELP US GROW !!

We reach over 6,800 subscribers worldwide and receive many nice notes about the 
learning and support from the group.Let friends and co-workers know they can 
subscribe to group at http://groups.google.com/group/excel-macros/subscribe

To unsubscribe from this group, send email to 
excel-macros+unsubscribegooglegroups.com or reply to this email with the words 
REMOVE ME as the subject.

Re: $$Excel-Macros$$ VBA VLookup dates

[Andy]

Heya,

Thanks for the reply!

At the moment the file is a bit too large to attach.

The dates I'm trying to match are in column C of the Access Data
sheet, which are checked with dates on the Front Page sheet (fortnight
range) and a number is looked up from these and placed into column H
of the Access Data sheet.

Hopefully this is a bit clearer, if not I'll try to trim the workbook
a little and upload it.

Cheers.

On Mar 20, 1:36 am, Dave Bonallack davebonall...@hotmail.com wrote:
 Hi Andy,
 Not quite sure what date fields you want to match to what.
 Is it possible to attach the worbook?
 Regards - Dave.

 Hey all,

 I'm trying to cut down on loading times by converting a load of
 formulae to code.

 I'm stuck on this particular formula -
 =IF(ISERROR(VLOOKUP((C3),fortnight,3,FALSE)),,VLOOKUP((C3),fortnight,
 3,FALSE))

 The main problem I seem to be having is how to amend the code to
 properly detect when date fields match.                                       
    
 _
 Link all your email accounts and social updates with Hotmail. Find out 
 now.http://windowslive.ninemsn.com.au/oneinbox?ocid=T162MSN05A0710G

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com


HELP US GROW !!

We reach over 6,800 subscribers worldwide and receive many nice notes about the 
learning and support from the group.Let friends and co-workers know they can 
subscribe to group at http://groups.google.com/group/excel-macros/subscribe

To unsubscribe from this group, send email to 
excel-macros+unsubscribegooglegroups.com or reply to this email with the words 
REMOVE ME as the subject.

$$Excel-Macros$$ VBA VLookup dates

[Andy]

Hey all,

I'm trying to cut down on loading times by converting a load of
formulae to code.

I'm stuck on this particular formula -
=IF(ISERROR(VLOOKUP((C3),fortnight,3,FALSE)),,VLOOKUP((C3),fortnight,
3,FALSE))

The main problem I seem to be having is how to amend the code to
properly detect when date fields match.

This is what I have so far for the VBA -
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lookFor As Range
Dim rng As Range
Dim col As Integer
Dim found As Variant

Set lookFor = Sheets(Access Data).Range(E2)
Set rng = Sheets(Front page).Range(fortnight)
col = 3

On Error Resume Next
found = Application.vlookup(lookFor.Value, rng, col, 0)
If IsError(found) Then
MsgBox lookFor   not found
Else: MsgBox The look-up value of   lookFor   is   found   in
column   col
End If
On Error GoTo 0

End Sub

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com


HELP US GROW !!

We reach over 6,800 subscribers worldwide and receive many nice notes about the 
learning and support from the group.Let friends and co-workers know they can 
subscribe to group at http://groups.google.com/group/excel-macros/subscribe

To unsubscribe from this group, send email to 
excel-macros+unsubscribegooglegroups.com or reply to this email with the words 
REMOVE ME as the subject.

$$Excel-Macros$$ Need help with Importing XML file

[Andy McGooner]

Hi All,

Apologies if this issue has been duplicated as it took a little bit of
a loopy on me when i tried to submit it last time and appeared to log
me out.

Anyway back to the question in hand, i am developing a macro that will
allow me (or the user) to select an xml file, import it into a
workbook as well as edit it so that it makes it easier to review by
the end user. The problem is that when i import i have to use the read
only option from the load options menu that pops up as the other 2
options dont work, which i believe is a limitation of 2003.

What i want to do is have as much user interaction as possible i.e.
more chance that they wont select the wrong import option. However
looking in groups and using the office assistant is not helping, can
anyone please enlighten to me to how i can bypass the option pop up,
so that once the button is pressed it does it all in one motion.

Here is the code i am using for this part off my macro:

StrXmlFileName = FONetIntXml.SelectedItems(1)

Application.Workbooks.OpenXML _
Filename:=StrXmlFileName, _
LoadOption:=xlXmlLoadPromptUser

Any assistance would be greatly appreciated.

Regards,

Andy

--~--~-~--~~~---~--~~
--
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at 
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 

To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain  @ jainayus...@gmail.com or
Ashish Jain @ 26may.1...@gmail.com

HELP US GROW !!

We reach over 6,500 subscribers worldwide and receive many nice notes about the 
learning and support from the group. Our goal is to have 10,000 subscribers by 
the end of 2009. Let friends and co-workers know they can subscribe to group at 
http://groups.google.com/group/excel-macros/subscribe
-~--~~~~--~~--~--~---

$$Excel-Macros$$ Re: Sorting a column

[A . for . Andy]

Thanks Dave

Has worked a charm! I have formatted the cells that data shall be
placed into (via macro) in advance, so should not be a problem in the
future.

Thank you kindly once again!

On Oct 23, 5:28 pm, Dave Bonallack [EMAIL PROTECTED] wrote:
 Hi,
 Just formatting a cell as number after the cell has received data, does not 
 change the format of the data.

 If all your data is numeric, then follow these steps to actually change your 
 data to numbers:

 1. Select Range A71:A1000
 2. Format as number.
 3. Enter 1 into an unused cell, then right-click that cell, and select Copy.
 4. Select Range A71:A1000, then right-click and select Paste Special.
 5. Select Multiply from the Operation options.
 6. Click OK.

 Now run your macro again, and it should sort as you want.

 If your data is a mix of numbers and letters, eg 1234ABCD, then the operation 
 is more complex, but still possible.

 Regards - Dave.





  Date: Thu, 23 Oct 2008 02:36:27 -0700
  Subject: $$Excel-Macros$$ Sorting a column
  From: [EMAIL PROTECTED]
  To: excel-macros@googlegroups.com

  Hi All

  I'm trying to code a worksheet so that when I press a button, it sorts
  the column in numerical order. I have been using the code

  Private Sub commandbutton12_click()
  Range(A71:U1000).Select
      Selection.Sort Key1:=Range(A71), Order1:=xlAscending,
  Header:=xlNo, _
          OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
  Range(A2).Select
  Call CommandButton1_Click
  End Sub

  However, whether the cells are formatted as numbers, general or text,
  the numbers sort in the following manner

  1
  10
  2
  20 etc

  rather than

  1
  2
  10
  20etc

  How can I sort them as per the second manner please?

  Many thanks in advance

 _- Hide 
 quoted text -

 - Show quoted text -

--~--~-~--~~~---~--~~
Visit the blog to download Excel tutorials at 
http://www.excel-macros.blogspot.com

To post to this group, send email to excel-macros@googlegroups.com
For more options, visit this group at 
http://groups.google.com/group/excel-macros?hl=en

Visit  Join Our Orkut Community at 
http://www.orkut.com/Community.aspx?cmm=22913620

Visit the blog to download Excel tutorials at 
http://www.excel-macros.blogspot.com

To Learn VBA Macros Please visit http://www.vbamacros.blogspot.com

To see the Daily Excel Tips, Go to:
http://exceldailytip.blogspot.com
-~--~~~~--~~--~--~---

$$Excel-Macros$$ Sorting a column

[A . for . Andy]

Hi All

I'm trying to code a worksheet so that when I press a button, it sorts
the column in numerical order. I have been using the code

Private Sub commandbutton12_click()
Range(A71:U1000).Select
Selection.Sort Key1:=Range(A71), Order1:=xlAscending,
Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range(A2).Select
Call CommandButton1_Click
End Sub

However, whether the cells are formatted as numbers, general or text,
the numbers sort in the following manner

1
10
2
20 etc

rather than

1
2
10
20etc

How can I sort them as per the second manner please?

Many thanks in advance

--~--~-~--~~~---~--~~
Visit the blog to download Excel tutorials at 
http://www.excel-macros.blogspot.com

To post to this group, send email to excel-macros@googlegroups.com
For more options, visit this group at 
http://groups.google.com/group/excel-macros?hl=en

Visit  Join Our Orkut Community at 
http://www.orkut.com/Community.aspx?cmm=22913620

Visit the blog to download Excel tutorials at 
http://www.excel-macros.blogspot.com

To Learn VBA Macros Please visit http://www.vbamacros.blogspot.com

To see the Daily Excel Tips, Go to:
http://exceldailytip.blogspot.com
-~--~~~~--~~--~--~---