Solved

Excel vba find xlvalues not working as expected

Posted on 2013-11-14
33
147 Views
Last Modified: 2014-10-24
I am working on a spreadsheet, which has lots of formulas that look up data from other worksheets and I now need to copy the resulting values to another worksheet.  
I have used the following code, which works correctly on my test workbook where it correctly finds the last resulting value despite there being formulas further down.
LastRow = Cells.Find(What:="*", after:=[A1], _
                  SearchOrder:=xlByRows, _
                  SearchDirection:=xlPrevious, LookIn:=xlValues).Row
      'Search for any entry, by searching backwards by Columns.
                  LastColumn = Cells.Find(What:="*", after:=[A1], _
                    SearchOrder:=xlByColumns, _
                    SearchDirection:=xlPrevious, LookIn:=xlValues).Column
                tlastrow = tlastrow + LastRow

However, when I transfer the code to the live workbook, it doesn't correctly find the last value.  The cell that it finds has no data at all along the row (other than a formula) and I cannot see any reason for it finding the cell that it does, other than possible formatting issues. There is a definite difference in the way in which the find function is working between the two spreadsheets
I will send both spreadsheets directly as required.
0
Comment
Question by:CraigStenton
  • 17
  • 14
33 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 39647900
Can you post that worksheet that has the data ? removing sensitive data.
There is an other way to look for last row but prefer to see the data and if the code is there it would be better.
gowflow
0
 

Author Comment

by:CraigStenton
ID: 39647949
Can I send the spreadsheets to you directly? - I don't want to post them for general viewing.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39647958
Nope !!! no email exchange it is against policy. just post the code don't worry
gowflow
0
 

Author Comment

by:CraigStenton
ID: 39648063
I'll ask the question again but make it private.  Please look out for it.
Thanks
Craig
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39648073
I tried your code and it works fine. If you are trapping the formula I need to know in what column and what row is it trapping it and where should it trap and what is in between blank cells I suppose ??
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39648076
Well even this when you make the question private I guess only you see it !!!
Don't waste your time we can fix this without you posting the data.
gowflow
0
 

Author Comment

by:CraigStenton
ID: 39650437
I have attached the spreadsheet with the majority of the sheets and data removed, but it still displays the same symptoms of finding the incorrect (IMO) last cell with data.
On the input tab, click on the "Print Quote" button and you will see that it finds 94 as the last row even though the last cell with data is 74.
PDMASTERSheetsRemoved.xls
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39650550
Noted I fixed your problem and had to remove couple of lines. as you were copying also and pasting a part after the last line if this is correct then please uncomment this instruction

'WS.Range("A1:" & WS.Cells(LastRow, LastColumn).Address).Copy _
        Destination:=Sheets("PrintTemp").Range("A" & tlastrow)

Open in new window


Basically what was happening is that the Row and Column search was happening in the sheet Input whereas you were expecting results in sheet Quote.

I forced the variable WS to be sheet Quote and linked all the instructions to be linked to WS this is called direct variable declaration not indirect.

The problem with the code you have is that it rely to default behavior and the small out of focus generate weired results. I only changed the part of Quote but you ought to change you entire routine to point to specific sheet.

If you are satisfied with the result then I can help you if you want to revamp your code entirely (presume this should be addressed in an other new question)

Rgds/gowflow
PDMASTERSheetsRemoved.xls
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39653099
Any chance to have tried the proposed solution ?
gowflow
0
 

Author Comment

by:CraigStenton
ID: 39697945
Hi Gowflow
Really sorry for the long delay in responding.  I have been away but am back and will be looking at this a little later.
I appreciate your comments, which have been successful, but I still have a little problem that is along the same lines.
I will be in touch later.
Regards
Craig
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39697981
ok fine. Glad to know your back. Pls let me know briefly when you get back to me on what was requested, what was reached so far and what is still pending for you. Just in plain emglish to have me refreshed this would save both of us time.
Rgds/gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39702460
If you want help you should not disappear endlessly !!!
gowflow
0
 

Author Comment

by:CraigStenton
ID: 39713798
Hi Goflow.
Sorry, I've been busy with other things.  I'm back now and I'm working on this.
I'll be in touch shortly.
0
 

Author Comment

by:CraigStenton
ID: 39713999
Hi
I have the following core code, which works and pastes the correct content but it pastes over the top of the previous pasted content with each sheet that it is called for.

Sub CopyandPaste(sheetn, tlastrow)
Dim LastColumn As Integer
Dim LastRow As Long
Dim LastCell As Range
Dim WS As Worksheet
Dim prntmp As Worksheet



 Set prntmp = Sheets("PrintTemp")
 Set WS = Sheets(sheetn)
 
       
 MsgBox "Copy and paste for " + sheetn
    If WorksheetFunction.CountA(Cells) > 0 Then
      'Search for any entry, by searching backwards by Rows.
      LastRow = WS.Cells.Find(What:="*", after:=[A1], _
                  SearchOrder:=xlByRows, _
                  SearchDirection:=xlPrevious, LookIn:=xlValues).Row
      'Search for any entry, by searching backwards by Columns.
      LastColumn = WS.Cells.Find(What:="*", after:=[A1], _
                    SearchOrder:=xlByColumns, _
                    SearchDirection:=xlPrevious, LookIn:=xlValues).Column
                    MsgBox "LastRow = " & (LastRow)
                MsgBox "Find on sheet & lastrow" & (sheetn & WS.Cells(LastRow, LastColumn).Address)

                MsgBox "Find on sheet2 & tlastrow" & (sheetn & "tlastrow = " & tlastrow)
       End If
       

        WS.Select
        WS.Range("A1:" & WS.Cells(LastRow, LastColumn).Address).Select
        Selection.Copy

        prntmp.Select

        Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
           ActiveSheet.Paste
       
        Application.CutCopyMode = False

                tlastrow = tlastrow + LastRow
                 
                ActiveWindow.View = xlPageBreakPreview
                ActiveSheet.HPageBreaks.Add Before:=Cells((tlastrow + 1), 1)
                ActiveWindow.View = xlNormalView

I am trying to set the cell to paste into with the following code but the content that is pasted is not correct.
prntmp.Range(prntmp.Cells((tlastrow + 1), 1).Address).Select

Thank you
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39714450
Sorry what is this code ? Is it the one I worked on ? It seems it is part ... how do you expect me to see a code after almost 1 month having worked on and then you come back with a different code and even part code as this Sub is not complete there is no end sub ....

Sorry but this is not serious. We are all swampt with work and if Expert take from their time to give you a solution you minimum should follow asap to get the matter clear and done with.

I will need the full code of this sub and a snapshot of what it is doing and what it should do as cannot guesss this way.

Rgds/gowflow
0
 

Author Comment

by:CraigStenton
ID: 39714524
Sorry goflow. The problem is with just the sub that I posted and I stripped out the superfluous to try to minimise what you had to look through thinking it would benefit you. No worries,  I'll post the full code.
Regards
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 29

Expert Comment

by:gowflow
ID: 39714550
pls post it the traditional way not copy paste !!! press on Code here in this window it will give you 2 items in braket like below:

 [ c o d e]  

... put you code here
[ / c o d e]


rgds/gowflow
0
 

Author Comment

by:CraigStenton
ID: 39721221
Hi Gowflow
I have attached the spreadsheet.
The main core code works where it pastes the correct content into the temp sheet, but it pastes over the top of the previous pasted content with each sheet that it is called for.
I have tried to control where the additional content is pasted with the following code but when I do this, the values aren't correct:
prntmp.Range(prntmp.Cells((tlastrow + 1), 1).Address).Select

Many thanks
PDMASTERNewButtonValuesChanged.xls
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39723428
Sorry not used to waste my time endlessly in debugging a re-worked code without understanding what you want. This is absurd. You ask a question I provide an answer that works exactly like what you requested, you disappear then comeback with a total new re-worked module then telling that it is not working !!! This is not a correct way to deal here.

Are we talking about the Sub CopyandPaste ???? in sheet Input ???

This is the code I provided to you

Sub CopyandPaste(sheetn, tlastrow)
Dim LastColumn As Integer
Dim LastRow As Long
Dim LastCell As Range
Dim WS As Worksheet

'Private mytlastrow As Integer
'Set mytlastrow = tlastrow

' ActiveWorkbook.Sheets(sheetn).Activate
Set WS = Sheets(sheetn)
 
 MsgBox "Copy and paste for " + sheetn
    If WorksheetFunction.CountA(Cells) > 0 Then
        'Search for any entry, by searching backwards by Rows.
        LastRow = WS.Range("A:A").Find(What:="*", After:=[A1], _
                      SearchOrder:=xlByRows, _
                      SearchDirection:=xlPrevious).Row
          'Search for any entry, by searching backwards by Columns.
        LastColumn = WS.UsedRange.Find(What:="*", After:=[A1], _
                        SearchOrder:=xlByColumns, _
                        SearchDirection:=xlPrevious, LookIn:=xlValues).Column
        MsgBox (LastRow)
        MsgBox WS.Cells(LastRow, LastColumn).Address
                    
        tlastrow = tlastrow + LastRow
        MsgBox "tlastrow = " & tlastrow
    End If
        
        'WS.Range("A1:" & WS.Cells(LastRow, LastColumn).Address).Copy _
        Destination:=Sheets("PrintTemp").Range("A" & tlastrow)

'MsgBox "LastColumn, LastRow" & LastColumn & LastRow
'Sheets("PrintTemp").hpagebreaks.add after:=

      'Copy and paste column widths
      If sheetn = "Quote" Then
        WS.Select
        WS.Range("A1:" & WS.Cells(LastRow, LastColumn).Address).Select
        Selection.Copy
        Sheets("PrintTemp").Select
        Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
        ActiveSheet.Paste
        Application.CutCopyMode = False
    End If
End Sub

Open in new window



You come back with a file that have this code for this same Sub

Sub CopyandPaste(sheetn, tlastrow)
Dim LastColumn As Integer
Dim LastRow As Long
Dim LastCell As Range
Dim WS As Worksheet
Dim prntmp As Worksheet


' ActiveWorkbook.Sheets(sheetn).Activate

'Private mytlastrow As Integer
'Set mytlastrow = tlastrow

 Set prntmp = Sheets("PrintTemp")
 Set WS = Sheets(sheetn)
 
 'ActiveWorkbook.Sheets(sheetn).Activate
        
 MsgBox "Copy and paste for " + sheetn
    If WorksheetFunction.CountA(Cells) > 0 Then
      'Search for any entry, by searching backwards by Rows.
      LastRow = WS.Cells.Find(What:="*", after:=[A1], _
                  SearchOrder:=xlByRows, _
                  SearchDirection:=xlPrevious, LookIn:=xlValues).Row
      'Search for any entry, by searching backwards by Columns.
      LastColumn = WS.Cells.Find(What:="*", after:=[A1], _
                    SearchOrder:=xlByColumns, _
                    SearchDirection:=xlPrevious, LookIn:=xlValues).Column
                    MsgBox "LastRow = " & (LastRow)
                MsgBox "Find on sheet & lastrow" & (sheetn & WS.Cells(LastRow, LastColumn).Address)

                MsgBox "Find on sheet2 & tlastrow" & (sheetn & "tlastrow = " & tlastrow)
       End If
       
        'WS.Range("A1:" & WS.Cells(LastRow, LastColumn).Address).Copy _
        Destination:=Sheets("PrintTemp").Range("A" & tlastrow)
        'ws.Sheets(sheetn).ws.Range("A1:" & Cells(LastRow, LastColumn).Address).Copy _
        'Destination:=Sheets("PrintTemp").Range("A" & tlastrow)

'ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
'LastRow.HPageBreaks.Add Before:=ActiveCell

'MsgBox "LastColumn, LastRow" & LastColumn & LastRow
'Sheets("PrintTemp").hpagebreaks.add after:=

      'Copy and paste column widths
      'If sheetn = "Quote Cabine" Then
        MsgBox ("Copy cells lastrow" & Cells(LastRow, LastColumn).Address)
        WS.Select
        WS.Range("A1:" & WS.Cells(LastRow, LastColumn).Address).Select
        'WS.Range("A1:" & WS.Cells(LastRow, LastColumn).Address).Copy _
        'Destination:=Sheets("PrintTemp").Range("A" & tlastrow)
        Selection.Copy
        'Sheets("PrintTemp").Select
        'ActiveSheet.Range("A" & (tlastrow + 1)).Select
        'Sheets("PrintTemp").Range("A" & (tlastrow + 1)).Activate
        'Set prntmp = Sheets("PrintTemp")
        'prntmp.Select
        MsgBox "Start paste cell " & ("A" & tlastrow)
        'MsgBox "Finish paste cell " & ("A" & LastRow)
        'Range("A" & (LastRow + 1)).Activate
        prntmp.Select
        'prntmp.Range(prntmp.Cells((tlastrow + 1), 1).Address).Select
        Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
        'prntmp.Range("A" & (tlastrow + 1)).PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
        ActiveSheet.Paste
        
        Application.CutCopyMode = False
    'End If
                tlastrow = tlastrow + LastRow
                MsgBox ("putting page break in at " & ("A" & (tlastrow + 1)))
                MsgBox ("putting page break in at " & ActiveSheet.Cells(LastColumn & tlastrow))
                MsgBox "lastcolumn = " & LastColumn
                MsgBox "tlastrow = " & tlastrow
                
                'prntmp.Select
                ActiveWindow.View = xlPageBreakPreview
                ActiveSheet.HPageBreaks.Add Before:=Cells((tlastrow + 1), 1)
                'ActiveSheet.HPageBreaks.Add Before:=ActiveSheet.Cells(LastColumn & tlastrow)
                'ActiveSheet.Cells(6, 5).Select
                ActiveWindow.View = xlNormalView

    'ActiveSheet.HPageBreaks.Add Before:=Cells(i, 1)
    'Set Worksheets("PrintTemp").HPageBreaks(Worksheets("PrintTemp").HPageBreaks.Count).Location = Worksheets("PrintTemp").Range("A" & (tlastrow + 1))
    'Set prntmp.HPageBreaks(Worksheets("PrintTemp").HPageBreaks.Count).Location = Worksheets("PrintTemp").Range("A" & (tlastrow + 1))
    
    
   
    
End Sub

Open in new window



As you can clearly see on has nothing to do with the second.

Can you please explain what you are trying to achieve ? By pressing the Pint Quote button what do you want to achieve explain it in plain English and leave me code this for you. If you like it then you accept if not you comment.

Regards

And I appreciate you reply as soon as possible to keep the momentum going.

"That is only if you need help from me" !!!
gowflow
0
 

Author Comment

by:CraigStenton
ID: 39723547
Hi Goflow
I just need to copy each of the sheets onto the print temp page one after the other, putting a page break in-between so that they all print out correctly formatted.
What is happening is that each iteration pastes over the top of the previous.
I have  a variable that holds the max row so that I can paste the next content after the previous has finished, not over the top of it.
I know the code has changed as I need to put in page breaks between each of the pages.
Thank you
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39724061
1 - ok I see you have lots of msgbox and these I suppose you have put to try to debug the code am I right ? Can these be removed as they are annoying !!

2 - When you press on Print Quote what are the sheet that you need and in what order ? I need all this not to discover it by code.

gowflow
0
 

Author Comment

by:CraigStenton
ID: 39724082
Sorry, I was going to tidy those msgboxes up to help you but then I thought it just better to not delay things. Yes, they are annoying but they help but yes, they can be removed.

The sheets are:
"Quote Cabine"
"Quote Duct"
"Quote Vanity"
"Quote Locker"

Thanks
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39724107
ok fine now we are talking !!!!
So let me get this correct

You press on Print Quote and you want it to:
1) Put the data that it finds in Tempsheet
2) it copy the whole sheet Quote Cabine and puts it in tmpsheet
3) then put a page break
4) Copy Quote Duct and put a page break
5) Copy Vanity and put a page break
6) Copy Locker and put a page break

Is this correct like stated ? If not pls put the corrections.
gowflow
0
 

Author Comment

by:CraigStenton
ID: 39724156
You press on Print Quote and it:
1) copy the whole sheet Quote Cabine and paste it in tmpsheet. then put a page break
4) Copy the whole sheet Quote Duct and paste it in tmpsheet.  Then put a page break
5) Copy the whole sheet Quote Vanity  and paste it in tmpsheet. Then put a page break
6) Copy the whole sheet Quote Locker and paste it in tmpsheet. Then put a page break

Thank you
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39725431
ok fine here it is.

The below file works as follows:
1) I have added a new button in Green in sheet Input called Print Quote (I left yours so you can see the difference)
2) the button will trigger a Sub that is located in the Module1 Called PrintQuote you may look at the code I have put comments so it is self explanatory the steps.
3) I have taken into consideration your situations when D9, L9, R9 Z10 (should be AA10) are positive so it take the sheet into consideration and ad it in the PrintTemp sheet.
4) Run it and see the results.
5) You will notice that some data is not aligned and this is normal as all your Quote sheets don't have the same columns width. So if you want to get the output final stadardised you only need to work on making each and every individual Quote sheet similar to all the others in terms of Columns width and you will get a perfect aligned final output.

Let me know your comments.
gowflow
PDMASTERNewButtonValuesChanged-V.xls
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39727333
Did you had a chance to have tried out the proposed solution ?
gowflow
0
 

Author Comment

by:CraigStenton
ID: 39735600
Hi Gowflow
That works well! Thank you.
I agree with you about the columns not being aligned between each of the sheets and the owner will have to move data for it to be consistent.
There is just one part that is not quite right that I would like to ask you about.  The logos seem to be stretched in different ways. I can edit the image properties to reset the size proportions afterwards but I don't see why the paste should stretch the images.
I am keen to see this finished and to give you the points that you deserve and look forward to your response.
Regards
Craig
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 39735953
Well your pictures all are not the same. If you make all the pictures the same size will it differ when you generate the quote ?
Do you have Excel 2010 ?? I have 2007 and no problem
gowflow
0
 

Author Comment

by:CraigStenton
ID: 39736016
Well, the images end up being different from the source so there is something that happens during the copy and paste.  Regardless, I am happy with your efforts so far and so I will accept your previous as a solution and look into it further.
Regards
Craig
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39736028
ok no problem even if the question is closed I will still look into the picture issue will make some test here. But you did not tell me you have Excel 2010 ?
gowflow
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40396476
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
 

Author Comment

by:CraigStenton
ID: 40401249
@ Gowflow
Sorry that I have not made contact - I have had to become focused on other things.
Thank you for your involvement where you did indeed provide very good solutions and you fully deserve the points.
Regards
Craig
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now