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]

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

Unchek the checkbox it's xlsm file

 

[Andrew Poulsom]

I don't see any MsgBox code in that workbook.

 

[cerins4ever]

Please see sheet2 comandbutton code it is the same as in Msgbox, i just inserted it in Msgbox to see result

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


     Sheet2.Range("A4", Cells(Rows.Count, "A").End(xlUp)).SpecialCells(xlCellTypeVisible).Cells(i1, 14).Value = 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

 

[Andrew Poulsom]

Why do you need to use SpecialCells(xlCellTypeVisible)?

 

[cerins4ever]

Something is wrong with colored section i can't figured out what


Sheet1.Range("A4", Cells(Rows.Count, "A").End(xlUp)).SpecialCells(xlCellTypeVisible)</pre>

 

[cerins4ever]

Becouse user are hiding the rows

 

[Andrew Poulsom]

Why would that matter? The Cells property doesn't care about hidden rows. Does this work for you?

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

 

[cerins4ever]

Why would that matter? The Cells property doesn't care about hidden rows. Does this work for you?

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

Run Time error 13
Type mismatch

 

[Andrew Poulsom]

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

 

[cerins4ever]

Look like code don't want to return from sheet1 to sheet2 Microsoft problem?