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

Sinem

Board Regular
Joined
Nov 8, 2015
Messages
63
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
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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.
 
Upvote 0
What exactly did you try and how didn't it work?
 
Upvote 0
Option Explicit
Public Cel As Range




Sub Makro1()


Dim fpath As String



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

..... (the rest is the same)
 
Upvote 0
Cel should not be in quotes here,
Code:
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.
Code:
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?
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top