Avatar of CraigStenton
CraigStenton asked on

Excel vba find xlvalues not working as expected

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.
Microsoft Excel

Avatar of undefined
Last Comment
CraigStenton

8/22/2022 - Mon
gowflow

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
ASKER
CraigStenton

Can I send the spreadsheets to you directly? - I don't want to post them for general viewing.
gowflow

Nope !!! no email exchange it is against policy. just post the code don't worry
gowflow
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER
CraigStenton

I'll ask the question again but make it private.  Please look out for it.
Thanks
Craig
gowflow

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
gowflow

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
CraigStenton

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
gowflow

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
gowflow

Any chance to have tried the proposed solution ?
gowflow
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER
CraigStenton

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
gowflow

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
gowflow

If you want help you should not disappear endlessly !!!
gowflow
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
CraigStenton

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.
ASKER
CraigStenton

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
gowflow

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
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER
CraigStenton

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
gowflow

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
ASKER
CraigStenton

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
gowflow

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
ASKER
CraigStenton

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
gowflow

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
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER
CraigStenton

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
gowflow

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
ASKER
CraigStenton

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
gowflow

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
gowflow

Did you had a chance to have tried out the proposed solution ?
gowflow
ASKER
CraigStenton

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
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER CERTIFIED SOLUTION
gowflow

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
CraigStenton

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
gowflow

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
Martin Liss

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
CraigStenton

@ 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