Set cell value as the VBA variable

    Hello,


    Can you use variables in the following way?


    Code
    If ThisWorkbook.Sheets("ClientWorkbench").Cells(ClientWorkbenchRow, Range(ClientWorkbench & ThisWorkbook.Sheets(ClientMatrixSht).Cells(MetricRw, 1).Value)).Value = "" Then


    Basically clientworkbenchrow is declared as a number, but wanted to get Range(ClientWorkbench & ThisWorkbook.Sheets(ClientMatrixSht).Cells(MetricRw, 1).Value) to give me ClientWorkbenchColumn rather than "ClientWorkbenchColumn" so that I can use it as a variable (ClientWorkbenchColumn is actually a number already which I wanted to use). Is that possible? Hope that makes sense, its been a long day!

    Re: Set cell value as the VBA variable


    It's possible - but, on its own, hard to determine if what you posted is correct. For example you have a sheet name 'ClientWorkbench' and a variable named 'ClientWorkbench' (...Range(ClientWorkbench...)



    However, if you substitute real values, and put a 1 in cell A1 of the worksheet

    Code
    If ThisWorkbook.Sheets(1).Cells(1, Range("A" & ThisWorkbook.Sheets(1).Cells(1, 1).Value)).Value = 1 Then
   MsgBox "1"
End If


    If will coimpile and run, and displays the message box.


    If you are having problems you need to test each portion. For example, add a Break to the line and run the code. When it stops copy this part to the Immediate window

    Code
    ThisWorkbook.Sheets(ClientMatrixSht).Cells(MetricRw, 1).Value


    Add a ? to the start, move to the end of the line and press enter. It should print the value of that cell, but I have a feeling it will error.

    Re: Set cell value as the VBA variable


    Thanks for replying.


    I think i should explain what i am up to. I know i can use a search to get this working, im more interested just to understand if this method is possible.


    i have a client matrix sheet (the name of which changes to the name of the client) which basically details what variables they want ( background, there could be a 100 variables and a client may be interested in any one of them). I then have a clientworkbench sheet, which is basically filtering a data sheet with what the client has specified.


    I have set all column headers and worksheets as variables so i can reference them directly ie ClientWorkbenchName is a declared variable that refers to the column header Name on the clientworkbench sheet (2 as its the second column which i found using match)


    Now on my matrix sheet, i have another column which has another list of separate variables running down it such as Name. In my original query, im trying to add clientworkbench (sheet name) to give clientworkbenchName (a declared integer value) and use that value (which is 2). Currently my code refers to it as "clientworbenchname" when i want it without the "" as its the declared value i want (2). Now i know a different way is to just to search for the variable value again, but like i say just wanted to know if this logic can work


    sorry for ranting on but there is a method in my madness!

    Re: Set cell value as the VBA variable


    I understand what you are doing - but posting a line of code containing variables is hard to intrepret with knowing what those variable refer to.


    My answer was just to show that, if you replaced your variables with actual values, the concept can work - and that really is all I can say about it based on what you have posted and described.

    Re: Set cell value as the VBA variable


    Yes, I know its difficult to follow - There are hundreds of variables and the code is already several thousand lines so I must apologise if im not making sense - im not trying to be obtuse!


    The values that I am getting back will always be string - by putting the string for the header name with the string of the sheetname - that gives me a stringname of the variable im trying to call. ie


    Code
    ClientWorkbenchRow = 2
ClientWorkbenchColumn = 5
MetricRw = 4
ClientMatrixSht = "BobMatrix"
ThisWorkbook.Sheets(ClientMatrixSht).Cells(MetricRw, 1).Value) = "Column"


If ThisWorkbook.Sheets("ClientWorkbench").Cells(ClientWorkbenchRow, Range(ClientWorkbench & ThisWorkbook.Sheets(ClientMatrixSht).Cells(MetricRw, 1).Value)).Value = "" Then


    Therefore, Range(ClientWorkbench & ThisWorkbook.Sheets(ClientMatrixSht).Cells(MetricRw, 1).Value)).Value I want to refer to the VBA variable ClientWorkbenchColumn, which has been declared as 5. I want it to give:




    Range(ClientWorkbench & ThisWorkbook.Sheets(ClientMatrixSht).Cells(MetricRw, 1).Value)).Value
    Range(ClientWorkbench & Column)).Value
    Range(ClientWorkbenchColumn)).Value
    =5


    Which would then translate to


    Code
    If ThisWorkbook.Sheets("ClientWorkbench").Cells(2, 5) = "" Then


    Let me know if im talking crazy still! Really appreciate the help.

    Re: Set cell value as the VBA variable


    Quote from M40wen;770828

    that gives me a stringname of the variable im trying to call


    It sounds to me like you need to put your variables into a Dictionary so you have a key to refer to them by, or use an object module to store them so that you can use CallByName.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    Re: Set cell value as the VBA variable


    Hi Rory,


    That sounds abit more complicated than what I was hoping for (I was hoping that the formula I had contained a typo!). Essentially I cant use it this way without a whole load more coding (and bearing in mind I already have hundreds of declared variables?)


    If not, I will just have to take the string value from the client matrix sheet, and use match to find it on the client workbench sheet again - which isn't a problem, just that I have to find something which I already have declared.


    Any advice appreciated.

    Re: Set cell value as the VBA variable


    There isn't a simpler method of evaluating a variable by name, I'm afraid.


    Might be easier for you to just use an actual table on a sheet to store your variables, so that you can then search by name.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login