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!

 

[cerins4ever]

What's in the relevant cells when you get that error? I tested the code before posting it, with I1 = 11.

Please see pic.

 

[cerins4ever]

when i1 are smaller then 4 error shows

 

[Andrew Poulsom]

What's the value of i1 when you get a type mismatch?

 

[cerins4ever]

What's the value of i1 when you get a type mismatch?

when i1 are smaller then 4
just corrected code and i'm testing it, do you work on TestVBA?

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


Sheet2.Cells(i1, 14).Value = Sheet1.Cells(Sheet2.Cells(i1, 1).Value, 2).Value


    i1 = i1 + 1


Loop

 

[Andrew Poulsom]

Yes I used your workbook.

Why are you using Range("A4:A" & LR).SpecialCells(xlCellTypeConstants).Count +4 rather than just LR?

 

[cerins4ever]

Yes I used your workbook.

Why are you using Range("A4:A" & LR).SpecialCells(xlCellTypeConstants).Count +4 rather than just LR?

I will test it!
Will it LR see hiden rows?

 

[Andrew Poulsom]

As I said before the Cells property doesn't care about hidden rows.

 

[cerins4ever]

when i'm using LR it dos't read last row so i need to use LR+1
But anyway it's amazing you shortened my code 4 x times and it's still working even better

 

[Andrew Poulsom]

You could have used:

Do While i1 <= LR

 

[cerins4ever]

You could have used:

Do While i1 <= LR

Fantastic!!!
Everything runs smooth
Last question about last row, when i hide last or last 5 rows it wont transfer data to them, why so?
do i need use:

SpecialCells(xlCellTypeConstants)