Values between sheets

[cerins4ever]

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?

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!

 

[Andrew Poulsom]

What do you want the message box to display? You current syntax is incorrect.

 

[cerins4ever]

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

Sheet1

 

[Andrew Poulsom]

Yes, but what do you want to display in the message box exactly?

 

[cerins4ever]

The date from sheet1 Colon B (pic. 2)

 

[Andrew Poulsom]

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).

 

[cerins4ever]

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

 

[cerins4ever]

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

 

[cerins4ever]

It's something like days calculation for delivery.
It's just too wide and i want to split it in to two sheets

Download TestVBA.xlsm

 

[Andrew Poulsom]

I can't download your file. It thinks it's an exe.