UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> How To Modify This Vba, Office 2007    
 
   
Frank Situmorang
post Jul 10 2019, 04:53 PM
Post#1



Posts: 1,203
Joined: 10-June 10



Hello,

I appreciate if any body can help me to modify this VBA in order to work for the one excel data record imported:

The VBA is working if the imported data is more than one record ( one row):

CODE
[/code]

Set wsDonorDetail = Sheets("DonorDetail")
Set wsInterface = Sheets("Interface")
wsDonorDetail.[d3].Resize(wsDonorDetail.[d3].End(xlDown).Row - 2, 6).Copy Destination:=wsInterface.[a2]
Sheets("Receipt").Select
Application.CutCopyMode = False
[code]


It won't work if the imported data is only one row. How if the content of the Sheets("DonorDetail") only one record/line, how can we say it on VBA not to use End(xlDown), but instead to go 6 columns to the right from D3.

Thank you for any help.

Frank

--------------------
Frank S
Highland, CA
Go to the top of the page
 
June7
post Jul 10 2019, 07:31 PM
Post#2



Posts: 648
Joined: 25-January 16



Tested the code. It works if there is data starting on row 3. Even if only row 3 has data. Rows 1 and 2 are ignored. If you want to provide workbook for analysis, follow instructions at bottom of my post.


--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
Frank Situmorang
post Jul 11 2019, 03:59 PM
Post#3



Posts: 1,203
Joined: 10-June 10



Thanks for your response June

I do not know where I am wrong June, it seems it stuck at this line.

wsDonorDetail.[d3].Resize(wsDonorDetail.[d3].End(xlDown).Row - 2, 6).Copy Destination:=wsInterface.[a2]

Actually it is not stuck but like hanging and working searching showing internet symbol. Probably because the imported data is only one line, so when it is doing end.xl down, it goes very long to the bottom and it is hard to copy that much of blank lines to the destination,

That is why I tried to make if statement but still not succesful.
CODE
[/code]
Set wsDonorDetail = Sheets("DonorDetail")
Set wsInterface = Sheets("Interface")
If Len(Range(("DonorDetail")"d4")).Value) = 0 then wsDonorDetail.[d3].Resize(wsDonorDetail.[d3].Row - 2, 6).Copy Destination:=wsInterface.[a2]
wsDonorDetail.[d3].Resize(wsDonorDetail.[d3].End(xlDown).Row - 2, 6).Copy Destination:=wsInterface.[a2]
Sheets("Receipt").Select
Application.CutCopyMode = False
[code]


May be you have any idea.

Thank you

Frank


--------------------
Frank S
Highland, CA
Go to the top of the page
 
June7
post Jul 11 2019, 04:13 PM
Post#4



Posts: 648
Joined: 25-January 16



I tested with workbook having data on only row 1 as well as data in rows 1 through 6. No errors. Code runs. I cannot replicate issue. Hence, suggestion you provide your file.

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
Frank Situmorang
post Jul 11 2019, 05:10 PM
Post#5



Posts: 1,203
Joined: 10-June 10



June7, You mean I send you the excel file?, what is the procedure, is it allowed except the image?

Thank you

Frank

--------------------
Frank S
Highland, CA
Go to the top of the page
 
June7
post Jul 11 2019, 05:40 PM
Post#6



Posts: 648
Joined: 25-January 16



Follow instructions at bottom of my post.

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
June7
post Yesterday, 01:41 PM
Post#7



Posts: 648
Joined: 25-January 16



You are using Excel as a database. Why not use Access instead?

Analyzing your Excel code is very annoying. I can't even select the button in Design Mode to view its properties, just runs code and then crashes.

You should attach files to a post instead of sending me by PM. Maybe someone else will look at.


This post has been edited by June7: Yesterday, 02:15 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
Frank Situmorang
post Yesterday, 02:04 PM
Post#8



Posts: 1,203
Joined: 10-June 10



Ok June7, here attached the files

I appreciate if anybody can help me

Thanks a lot

Frank
Attached File(s)
Attached File  Testing.zip ( 196.62K )Number of downloads: 2
 

--------------------
Frank S
Highland, CA
Go to the top of the page
 
ADezii
post Yesterday, 02:49 PM
Post#9



Posts: 2,457
Joined: 4-February 07
From: USA, Florida, Delray Beach


I appears as though you are correct in that the Code is 'hanging' in exactly the location where you said it was, namely the 3rd Line from the bottom in the Macro Frank2. A simple fix would be to check for the existence of Data in Row [D4] of the Donor Detail Worksheet. If Data exists there then there is > 1 Row of Data, if not only a single Row, so fill in the appropriate action to take.
CODE
'***************** CODE ABOVE HAS INTENTIONALLY BEEN OMITTED *****************
    Set wsDonorDetail = Sheets("DonorDetail")
    Set wsInterface = Sheets("Interface")
    
    If wsDonorDetail.Range("D4").Value <> "" Then
      wsDonorDetail.[d3].Resize(wsDonorDetail.[d3].End(xlDown).Row - 2, 6).Copy _
                                Destination:=wsInterface.[a2]
    Else
      'Code to execute if > 1 Line starting from D3 on wsDonorDetail
    End If
    
    Sheets("Receipt").Select
    Application.CutCopyMode = False
End Sub
Go to the top of the page
 
Debaser
post Today, 02:38 AM
Post#10



Posts: 124
Joined: 11-October 18



It's generally better to go up from the bottom of the sheet to find the data you need. Also, you don't need to use CutCopyMode when you copy and paste in one line like that:

CODE
Set wsDonorDetail = Sheets("DonorDetail")
Set wsInterface = Sheets("Interface")
With wsDonorDetail
   .Range("d3", .Cells(.Rows.Count, "D").End(xlUp).Offset(, 2)).Copy Destination:=wsInterface.Range("a2")
End With
Sheets("Receipt").Select
Go to the top of the page
 
Frank Situmorang
post Today, 02:39 PM
Post#11



Posts: 1,203
Joined: 10-June 10



Thank you Adezii.

The code that I do not know is not for 'Code to execute if > 1 Line starting from D3 on wsDonorDetail, but if only 1 line on the wsDonorDetail.

How can we omit End(xlDown), but instead it just copy that line in D3, because the data is just 1 record.

Thank you Adezii for helping me again.

Frank

--------------------
Frank S
Highland, CA
Go to the top of the page
 
ADezii
post Today, 02:50 PM
Post#12



Posts: 2,457
Joined: 4-February 07
From: USA, Florida, Delray Beach


Since it is only a single Row, you can perform a direct Copy Operation, as in:
CODE
'***************** CODE ABOVE HAS INTENTIONALLY BEEN OMITTED *****************
    Set wsDonorDetail = Sheets("DonorDetail")
    Set wsInterface = Sheets("Interface")

    If wsDonorDetail.Range("D4").Value <> "" Then
      wsDonorDetail.[d3].Resize(wsDonorDetail.[d3].End(xlDown).Row - 2, 6).Copy _
                                Destination:=wsInterface.[a2]
    Else
      'Code to execute if > 1 Line starting from D3 on wsDonorDetail
      With wsInterface
        .Range("A2") = wsDonorDetail.Range("D3")
        .Range("B2") = wsDonorDetail.Range("E3")
        .Range("C2") = wsDonorDetail.Range("F3")
        .Range("D2") = wsDonorDetail.Range("G3")
        .Range("E2") = wsDonorDetail.Range("H3")
      End With
    End If
    
    Sheets("Receipt").Select
    Application.CutCopyMode = False
End Sub
Go to the top of the page
 
Frank Situmorang
post Today, 03:22 PM
Post#13



Posts: 1,203
Joined: 10-June 10



It is too complicated I think Adezii, because it will work perfectly if the imported data in worksheet Donor Detail is more than one line.

Please download my file and try to run the macro. the imported data is from Book2 and Book4

Thank you
Attached File(s)
Attached File  Testing.zip ( 196.62K )Number of downloads: 0
 

--------------------
Frank S
Highland, CA
Go to the top of the page
 
Frank Situmorang
post Today, 03:24 PM
Post#14



Posts: 1,203
Joined: 10-June 10



Thanks Debaser for your code, however I have tried it but the result is zero, I do not know where I am wrong.

Frank

--------------------
Frank S
Highland, CA
Go to the top of the page
 
Frank Situmorang
post Today, 04:03 PM
Post#15



Posts: 1,203
Joined: 10-June 10



Adezii,

Your code is right, please apologize me. It works perfectly. Thank you very much, God bless you.

Frank

--------------------
Frank S
Highland, CA
Go to the top of the page
 
ADezii
post Today, 05:57 PM
Post#16



Posts: 2,457
Joined: 4-February 07
From: USA, Florida, Delray Beach


yw.gif , Good Luck with your Project!
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    15th July 2019 - 07:13 PM