Values between sheets

cerins4ever

New Member
Joined
Jun 5, 2014
Messages
35
Hi guys, i have one problem with data transfer between sheets, hope someone professional will give me answer.
I have MsgBox code which is not working, where is problem?
Is it problem with that in the code are line .End(xlUp).SpecialCells(xlCellTypeVisible) or what?

Code:
Private Sub CommandButton1_Click()

Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row

Dim i1 As Integer
i1 = 1
Do While i1 < Sheet2.Range("A4:A" & LR).SpecialCells(xlCellTypeConstants).Count + 1


MsgBox Sheet1.Range("A4", Cells(Rows.Count, "A").End(xlUp)).SpecialCells(xlCellTypeVisible).Cells(Sheet2.Range("A4", Cells(Rows.Count, "A").End(xlUp)).SpecialCells(xlCellTypeVisible).Cells(i1, 1).Value - 3, 2).Value


    i1 = i1 + 1
Loop
End Sub

Thanks a lot!
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
it is date what i want to display form sheet1.
See image: code reads the ID code from page2 (1st pic.), then find the date from page1 (pic. 2nd.) and display it.
Code is like searching tool. I register data with id codes and then transfer dates to sheet 2 which is in mixed sequence.
I know data id code and with code i can search for date, because ID code are always the same as row number


Sheet2
image.jpg


Sheet1
Date.jpg
 
Upvote 0
Can you post your actual data rather than images please so that I can copy it from the Forum and paste it into Excel.

I do note that you haven't qualified the Cells property in your code. You should precede it with Sheet1. or Sheet2. as appropriate (and as you have done for the Range property).
 
Upvote 0
Can you post your actual data rather than images please so that I can copy it from the Forum and paste it into Excel.

I do note that you haven't qualified the Cells property in your code. You should precede it with Sheet1. or Sheet2. as appropriate (and as you have done for the Range property).


I will share a link where can download test excel file

http://www.datafilehost.com/d/0421fd71
 
Last edited:
Upvote 0
Can you post your actual data rather than images please so that I can copy it from the Forum and paste it into Excel.

I do note that you haven't qualified the Cells property in your code. You should precede it with Sheet1. or Sheet2. as appropriate (and as you have done for the Range property).

Please see blue comand button in first page it's working perfectly, but in sheet2 (Pasūtījums) comand button pup up with error
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,245
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top