How to use the same variable from the previous Code but same Module??

[Sinem]

Hi,

does anybody know how to use the variable "Cel" in the PrivatSub Code???? I tried it with declaring "Cel" as Public Variable but it does not work.



Option Explicit

Sub Makro1()


Dim fpath As String
Dim Cel As Range


fpath = Sheets("Sheets1").Range("E1").Value

For Each Cel In Range("B3:S3")
If InStr(Cel, ".csv") Then WorkOnCSV fpath, Cel
Next Cel
End Sub


Private Sub WorkOnCSV(fpath As String, fnameCell As Range)

Dim tool As Workbook
Dim daten As Workbook
Dim col As Variant


Set tool = ThisWorkbook
Set daten = Workbooks.Open(fpath & fnameCell, ReadOnly:=True)


Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual


daten.Activate
Let col = Application.Match("Price(12y)", Rows(1), 0)


If Not IsError(col) Then
Cells(2, col).Resize(61).Copy _
Destination:=tool.Worksheets("Sheets2").Range("L11")

Else: MsgBox ("Name not Found")
End If

tool.Activate

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

tool.Worksheets("Sheets2").Range("N12").End(xlDown).Copy _
Destination:=tool.Worksheets("Sheets1").Range("Cel").Offset(1, 0) <---- here is the error
daten.Close savechanges:=False


Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

 

[Norie]

Where did you declare Cel as Public?

If it was at the top of the module and not in any sub then it should be available throughout the module.

 

[Sinem]

Hi,

I just tried it once. It did not work.

 

[Norie]

What exactly did you try and how didn't it work?

 

[Sinem]

Option Explicit
Public Cel As Range




Sub Makro1()


Dim fpath As String



fpath = Sheets("Sheet1").Range("E1").Value

..... (the rest is the same)

 

[Norie]

Cel should not be in quotes here,

tool.Worksheets("Sheets2").Range("N12").End(xlDown).Copy _
Destination:=tool.Worksheets("Sheets1").Range("Cel").Offset(1, 0) <---- here is the error

in fact this might be all you need.

tool.Worksheets("Sheets2").Range("N12").End(xlDown).Copy _
Destination:=Cel.Offset(1, 0)

By the way, you do realise you are already passing Cel to the sub WorkOnCSV via the argument fnameCell?

 

[Sinem]

Ohhh man you are saver. Thank you very much!!!