How to use "range" with a text value of an integer value

[KhallP]

I need to use the "range" parameter to select a specific cell, it happens that when I "run" the program it gives an error and says "The specified dimension is not valid for the current graph"


Error
--------------------------------------------
If Range("H(CStr(k))").Value = "" Then
-------------------------------------------
Workbooks(myRecentFile).Worksheets(counter).Range("W110").Copy
Workbooks("EEC QEC.xlsm").Worksheets(counter2).Range("H(CStr(k))").Paste
Workbooks(myRecentFile).Worksheets(counter).Range("AI110").Copy
Workbooks("EEC QEC.xlsm").Worksheets(counter2).Range("I(CStr(k))").Offset(0, 1).Paste

Else
Do Until ActiveCell.Value = ""
ActiveCell.Offset(1, 0).Select
res = res + 1
Loop

Workbooks(myRecentFile).Worksheets(counter).Range("W110").Copy
Workbooks("EEC QEC.xlsm").Worksheets(counter2).Range("H(CStr(k + res))").Paste
Workbooks(myRecentFile).Worksheets(counter).Range("AI110").Copy
Workbooks("EEC QEC.xlsm").Worksheets(counter2).Range("I(CStr(k + res))").Offset(0, 1).Paste

End If[/CODE]

 

[HaHoBe]

Hi KhallP,

If Range("H" & CStr(k)).Value = "" Then

Ciao,
Holger

 

[KhallP]

Hi KhallP,

If Range("H" & CStr(k)).Value = "" Then

Ciao,
Holger

Thanks man!

 

[KhallP]

Hi KhallP,

If Range("H" & CStr(k)).Value = "" Then

Ciao,
Holger

Now in that line appear:


"object doesn't support this property or method"


If sh.Name = "QEC 1.2 - montagem" Or sh.Name = "QEC 2.2 -SALA LIMPA" Or sh.Name = "QEC 2.4 Logística" Or sh.Name = "QEC 4.1 - MONTAGEM MANUAL(past)" Or sh.Name = "QEC 4.2 - Desmoldagem" Or sh.Name = "QEC 4,3 - RTM" Or sh.Name = "QEC 4,4 - HOT DRAPE" Then
If Range("H" & CStr(k)).Value = "" Then
Workbooks(myRecentFile).Worksheets(counter).Range("W110").Copy
Workbooks("EEC QEC.xlsm").Worksheets(counter2).Range("H" & CStr(k)).Paste
Workbooks(myRecentFile).Worksheets(counter).Range("AI110").Copy
Workbooks("EEC QEC.xlsm").Worksheets(counter2).Range("I" & CStr(k)).Offset(0, 1).Paste

Else
Do Until ActiveCell.Value = ""
ActiveCell.Offset(1, 0).Select
res = res + 1
Loop

Workbooks(myRecentFile).Worksheets(counter).Range("W110").Copy

Error
-----------------------------------------------------------------------------------------
Workbooks("EEC QEC.xlsm").Worksheets(counter2).Range("H" & CStr(k + res)).Paste
------------------------------------------------------------------------------------------

Workbooks(myRecentFile).Worksheets(counter).Range("AI110").Copy
Workbooks("EEC QEC.xlsm").Worksheets(counter2).Range("I" & CStr(k + res)).Offset(0, 1).Paste

End If

 

[HaHoBe]

Hi KallP,

the lines you mention by now to raise an exception have not been part of the opening question.

You do not need to convert a numeric value to a string in order to use it either in a range or cells object - they both expect numbers for the rows. And as I cannot see where counter2 gets any value I regret but I think nobody would be able to answer that problem only by looking at this thread..

Ciao,
Holger

 

[KhallP]

Hi KallP,

the lines you mention by now to raise an exception have not been part of the opening question.

You do not need to convert a numeric value to a string in order to use it either in a range or cells object - they both expect numbers for the rows. And as I cannot see where counter2 gets any value I regret but I think nobody would be able to answer that problem only by looking at this thread..

Ciao,
Holger

Public Sub WriteCells()
    
    res = 0
    
    Sheets("QEC 1.2 - montagem").Select
    k = Cells(Rows.Count, "H").End(xlUp).Row
    
    ws = Workbooks(myRecentFile).Sheets.Count
    ws2 = Workbooks("EEC QEC.xlsm").Sheets.Count
    
    For counter = 1 To ws
        For counter2 = 1 To ws2
            Set ch = Workbooks(myRecentFile).Worksheets(counter)
            Set sh = ActiveWorkbook.Worksheets(counter2)
            If ch.Name = "QEC 12 IF" Or ch.Name = "QEC 22 IF" Or ch.Name = "QEC 24 IF" Or ch.Name = "QEC 41 IF" Or ch.Name = "QEC 42 IF" Or ch.Name = "QEC 43 IF" Or ch.Name = "QEC 44 IF" Then
                If sh.Name = "QEC 1.2 - montagem" Or sh.Name = "QEC 2.2 -SALA LIMPA" Or sh.Name = "QEC 2.4 Logística" Or sh.Name = "QEC 4.1 - MONTAGEM MANUAL(past)" Or sh.Name = "QEC 4.2 - Desmoldagem" Or sh.Name = "QEC 4,3 - RTM" Or sh.Name = "QEC 4,4 - HOT DRAPE" Then
                    If Range("H & CStr(k + 1)").Value = "" Then
                        Workbooks(myRecentFile).Worksheets(counter).Range("W110").Copy
                        Workbooks("EEC QEC.xlsm").Worksheets(counter2).Range("H & CStr(k + 1)").Paste
                        Workbooks(myRecentFile).Worksheets(counter).Range("AI110").Copy
                        Workbooks("EEC QEC.xlsm").Worksheets(counter2).Range("I & CStr(k + 1)").Offset(0, 1).Paste
                    
                    Else
                    Do Until ActiveCell.Value = ""
                        ActiveCell.Offset(1, 0).Select
                        res = res + 1
                    Loop
                
                        Workbooks(myRecentFile).Worksheets(counter).Range("W110").Copy
                        Workbooks("EEC QEC.xlsm").Worksheets(counter2).Range("H & CStr(k + res + 1)").Paste
                        Workbooks(myRecentFile).Worksheets(counter).Range("AI110").Copy
                        Workbooks("EEC QEC.xlsm").Worksheets(counter2).Range("I & CStr(k + res + 1)").Offset(0, 1).Paste
                
                    End If
                End If
            End If
        Next counter2
    Next counter
    
End Sub

 

[HaHoBe]

Hi KhallP,

 If Range("H & CStr(k + 1)").Value = "" Then

should read

 If Range("H" & k + 1).Value = "" Then

Also for the other lines of code.

Maybe spread this on the other threads you started as well.

Holger

 

[Fluff]

Duplicate to: "object doesn't support this property or method"

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread