Need help with variable - can I define one variable to two cells with if function?

[ironny90]

Hi there! Learned so much in this forum about vba. Just want to say thank you first.

The issue I run into this time is about if I can assign one variable to two cells with if statement.

To be precise, I have a list of data validation of yes and no (in cell C12). If yes, it means the ID of the product is in the report name, and I can use the ID (in cell A2) to find the corresponding report and open it. If no, it means the ID is not in the report name, so I have to use the product name (in cell A1) to find the report. So the code is like this:

if range ("C12").value = "yes" then
rng = range("A2").value
else
rng = range("A1").value

then I will use find to look for rng

It didn't work. I wonder if I can assign rng to two cells? How can I get around this?

Thank you in advance!

 

[Fluff]

In what way didn't it work?
Also did you declare rng & if so how?

 

[ironny90]

In what way didn't it work?
Also did you declare rng & if so how?

Hi Fluff! Thank you for your quick response!

Normally I don't declare a variable if it equals to a cell value, like rng and StrY I just make it equal to a value.

It didn't open the report it's supposed to open and skipped the whole section of code. So what happens next is:

Set wbk4 = ActiveWorkbook
Sheets("Summary").Activate
rng = Range("A2").Value 'A2 has the BU ID
wbk6.Activate
Sheets("Step 1_Report Name").Select
  Dim efound As Range
    Set efound = Range("A:A").Find(What:=rng, LookIn:=xlValues) 'look for report
        If Not efound Is Nothing Then
StrY = efound.Value
Dim myPathB As String
Dim myfileB As String
wbk6.Activate
myPathB = filepath3 & "\"
myfileB = myPathB & StrY

If myfileB <> "" Then
    Workbooks.Open Filename:=myfileB 'open report and get value from report

The code above works. Currently I have the same code repeated twice (used a new set of variables, i.e. myPathA and myPathB) with cell A1 and cell A2.

Not sure if there is a better way to use the code just once because everything else is exactly the same except for using the value of A1 or A2.

 

[Fluff]

Can you please post all your code.

 

[ironny90]

If Range("C14").Value = "No" Then 'to choose yes or no
Set wbk3 = ActiveWorkbook
Dim filepath1 As String
Dim filepath2 As String
filepath1 = Sheets("Step 1_Report Name").Range("D2").Value
filepath2 = Sheets("Step 2_Generator").Range("C12").Value
Set fso = CreateObject("scripting.filesystemobject")
Set ff = fso.Getfolder(filepath2)
For Each file In ff.Files
Workbooks.Open file
Set wbk1 = ActiveWorkbook
Sheets("Summary").Activate
rngY = Range("A1").Value 'A1 has the product name
wbk3.Activate
Sheets("Step 1_Report Name").Select 'this tab has all the report names, so the idea is to look for either ID or product name to find the correct report name
  Dim mfound As Range
    Set mfound = Range("A:A").Find(What:=rngY, LookIn:=xlValues)
    If mfound = "" Then
    wbk1.Activate
    MsgBox rngY & " is not matched with report. Please update manually."
    ActiveWorkbook.Save
    ActiveWorkbook.Close
    Else
StrX = mfound.Value
Dim myPathA As String
Dim myfileA As String
wbk3.Activate
myPathA = filepath1 & "\"
myfileA = myPathA & StrX

If myfileA <> "" Then
    Workbooks.Open Filename:=myfileA
Set wbk2 = ActiveWorkbook
Sheets("Assumptions Report").Cells.Copy
wbk1.Activate
Sheets("5-22").Select
Range("A1").PasteSpecial xlPasteAll
Range("A1").Select
End If
Sheets("Summary").Select
ActiveWorkbook.Save
ActiveWorkbook.Close
wbk2.Close
End If
Next

'Start of code for portfolios with BU ID
Else
Set wbk6 = ActiveWorkbook
Dim filepath3 As String
Dim filepath4 As String
filepath3 = Sheets("Step 1_Report Name").Range("D2").Value
filepath4 = Sheets("Step 2_Generator").Range("C12").Value
Set fso = CreateObject("scripting.filesystemobject")
Set ff = fso.Getfolder(filepath4)
For Each file In ff.Files
Workbooks.Open file
Set wbk4 = ActiveWorkbook
Sheets("Summary").Activate
rngH = Range("A2").Value 'A2 has the BU ID
wbk6.Activate
Sheets("Step 1_Report Name").Select
  Dim efound As Range
    Set efound = Range("A:A").Find(What:=rngH, LookIn:=xlValues)
        If Not efound Is Nothing Then efound.Select
StrY = efound.Value

Dim myPathB As String
Dim myfileB As String
wbk6.Activate
myPathB = filepath3 & "\"
myfileB = myPathB & StrY

If myfileB <> "" Then
    Workbooks.Open Filename:=myfileB
Set wbk5 = ActiveWorkbook
Sheets("Assumptions Report").Cells.Copy
wbk4.Activate
Sheets("5-22").Select
Range("A1").PasteSpecial xlPasteAll
Range("A1").Select
End If
Sheets("Summary").Select
ActiveWorkbook.Save
ActiveWorkbook.Close
wbk5.Close
Next

The code is very long so I removed parts of it. Currently it runs well, but it's repeated twice as you can tell.

 

[Fluff]

The part you said wasn't working, isn't even in that code.

 

[ironny90]

Set wbk3 = ActiveWorkbook
ID = Range("C14").Value 'choose yes or no for ID
Dim filepath1 As String
Dim filepath2 As String
filepath1 = Sheets("Step 1_Report Name").Range("D2").Value
filepath2 = Sheets("Step 2_Generator").Range("C12").Value
Set fso = CreateObject("scripting.filesystemobject")
Set ff = fso.Getfolder(filepath2)
For Each file In ff.Files
Workbooks.Open file
Set wbk1 = ActiveWorkbook
Sheets("Summary").Activate
If ID = "No" then
rng = Range("A1"). Value 'A1 has the product name
Else
rng = Range("A2"). Value 'I tried the code like this but it didn't work
wbk3.Activate
Sheets("Step 1_Report Name").Select 'this tab has all the report names, so the idea is to look for either ID or product name to find the correct report name
  Dim mfound As Range
    Set mfound = Range("A:A").Find(What:=rng, LookIn:=xlValues)
    If mfound = "" Then
    wbk1.Activate
    MsgBox rng & " is not matched with report. Please update manually."
    ActiveWorkbook.Save
    ActiveWorkbook.Close
    Else
StrX = mfound.Value
Dim myPathA As String
Dim myfileA As String
wbk3.Activate
myPathA = filepath1 & "\"
myfileA = myPathA & StrX

If myfileA <> "" Then
    Workbooks.Open Filename:=myfileA
Set wbk2 = ActiveWorkbook
Sheets("Assumptions Report").Cells.Copy
wbk1.Activate
Sheets("5-22").Select
Range("A1").PasteSpecial xlPasteAll
Range("A1").Select
End If
Sheets("Summary").Select
ActiveWorkbook.Save
ActiveWorkbook.Close
wbk2.Close
End If
Next
End sub ()

Just revised the code. The code above is what I tried. Thank you!!!

 

[Fluff]

There is nothing in that code that shows if you have declared the variable rng. Have you done that & if so how did you declare it?

 

[ironny90]

There is nothing in that code that shows if you have declared the variable rng. Have you done that & if so how did you declare it?

That's a good question. I would add Dim rng as Variable

The code right now didn't declare the variable rng, but it still runs, so I am not sure if it's a must to declare rng?

For a range or string, I will declare those. I didn't declare StrX either but it still works.

I am new to vba (started like a month and half ago) so my coding is not very structured?

 

[Fluff]

What sort of values are in A1 & A2?