String and Value workbook reference

**jsp_1951** · 10-15-2013, 05:33 PM

Instead of editing my Excel macro to change a string, I would like to edit another worksheet of the workbook and read the variable name from that information sheet into my macro.

So if my macro contains a line;

And Cells(1, i) <> "FLOWA2" _

and on another run I want it to be "FLOWA3" , I enter FLOWA3 in an information worksheet 'Sheet1, cell A1'. How is it referenced in the macro???

Similarly for a value. If my macro formula subtracts 1.23 as below

ActiveCell.FormulaR1C1 = "=RC[-2]-1.23"

I would like to be able to subtract 4.56 by entering that number in an information worksheet 'Sheet1, cell B1'

Thanks,
Jim

**Bilbo_Baggins** · 10-15-2013, 07:00 PM

This probably makes perfect sense to you, as you see it and read it.

However, it seems a bit murky (to me at least) absent looking at it on my screen.

Can you take another stab at explaining it, or upload a sample workbook please?

**alansidman** · 10-15-2013, 07:50 PM

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(I have added them for you this time. Please read our rules and abide by them in the future.)

**jsp_1951** · 10-15-2013, 09:13 PM

Sorry for the unclear request, it's probably because I am not a programmer and over several months have created a very convoluted and inefficient macro, by recording sequences of key strokes, pasting them together and adding code from kind helpers on this site. The equations are all hard coded with numerical values and names that only work for the current dataset, e.g. subtract 2.5 from each cell of column "ABC". When I get another data set I go in and edit the macro's hard coded values and strings. Although there are only 5 or 6 values to change they are in 5 different macros in 15 or 20 different places and I inevitably miss one. Instead of a hardcoded value in the macro equation I want a pointer that will go to another sheet in the workbook and read the values to use. The proper approach would be to write the macros with variables. My poorboy approach is to retrieve a string or value from a specific place in another sheet of the workbook. In effect the macro says "In Sheet 2, Subtract the value that is in cell A1 of Sheet1, form every cell in the column of Sheet2 that has the name as given in cell B1 of Sheet1"

Would it be easier to explain to me how to substitute a variable for the hardcoded strings and numbers, and then read a file that updates the variables????

**Bilbo_Baggins** · 10-15-2013, 10:09 PM

On the other hand, attached is a macro enabled file that uses some code (initiated by a button click) that will count all the cells with numeric contents and adds the value from the "Change Value" cell.
Any number of cells in column A can have values and they can be in any position in column A.
Text values in column A will be ignored.
Any error, including if the value in Change value is not numeric, will end the code.
If you want to add values, place a positive value in Change Value cell.
If you want to subtract values, place a negative value in Change Value cell.

Edit: Needed to add something to the file.
If you already downloaded it, delete it.
I am trying to upload the corrected version

**Bilbo_Baggins** · 10-15-2013, 09:26 PM

arguably, the easiest way to do it is with a fixed reference.

For example

=A1+$D$1

The D1 reference is fixed in position so you can put this formula in B1 and fill down and column B will be filled with the results.
In the D1 reference, the column D is fixed static by use of the $ sign in fron of the D and the row is likewise fixed in position by the $ sign in fron of the 1
Technically, for the above example, the only $ sign technically needed is the one in front of the number, but I added both for demonstration purposes.
Every time you change the value in D1, the calculated result would change in column B.

**jsp_1951** · 10-16-2013, 11:11 AM

I guess I don't understand, I am familiar with sheet and cell reference in a spreadsheet but I can't seem to apply the concept in a macro. Below is the macro that I use to select a subst of the colums. When I want to select different colums, I edit the col names below. These names are used in several different way in later macros, so I need to edit the there as well.

'
' Copy Las Data to QCData sheet
'
'
    Sheets("LAS Load").Select
    Cells.Select
    Selection.Copy
    Sheets("QCData").Select
    Range("A1").Select
    ActiveSheet.Paste
' Rename ~A to CutOff
    Range("A1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "CutOff"
'
' This section deletes columns not needed from raw data
' Add/change mnemonics below to specify channels to be saved
'
Sheets("QCData").Select
Dim i As Long
For i = Cells(1, Columns.Count).End(xlToLeft).Column To 1 Step -1
If Cells(1, i) <> "CutOff" _
And Cells(1, i) <> "TIME" _
And Cells(1, i) <> "LSPD" _
And Cells(1, i) <> "ADPTH" _
And Cells(1, i) <> "TEMP" _
And Cells(1, i) <> "FLOWA3" _
And Cells(1, i) <> "FLOWB3" _
And Cells(1, i) <> "FLOWC3" _
And Cells(1, i) <> "FLOWA2" _
And Cells(1, i) <> "FLOWB2" _
And Cells(1, i) <> "FLOWC2" _
Then Columns(i).Delete
Next i
'
' End Sub

I was hoping to add a worksheet to the workbook (here named INFO) in which I can change column names and function variables that are then read by the macro. As an example in the INFO sheet I might change the name of the last column I wish to keep by entering its name in A11. The following reference in the macro does not work as intended.

'
' This section deletes columns not needed from raw data
' Add/change mnemonics below to specify channels to be saved
'
Sheets("QCData").Select
Dim i As Long
For i = Cells(1, Columns.Count).End(xlToLeft).Column To 1 Step -1
If Cells(1, i) <> "CutOff" _
And Cells(1, i) <> "TIME" _
And Cells(1, i) <> "LSPD" _
And Cells(1, i) <> "ADPTH" _
And Cells(1, i) <> "TEMP" _
And Cells(1, i) <> "FLOWA3" _
And Cells(1, i) <> "FLOWB3" _
And Cells(1, i) <> "FLOWC3" _
And Cells(1, i) <> "FLOWA2" _
And Cells(1, i) <> "FLOWB2" _
And Cells(1, i) <> "=INFO!$A$11" _
Then Columns(i).Delete
Next i
'
' End Sub

**Bilbo_Baggins** · 10-15-2013, 10:18 PM

Here is te fixed file

**Norie** · 10-16-2013, 11:25 AM

Jim

For both examples in your first post just replace the values with the appropriate references.

And Cells(1, i) <> Sheets("Sheet1").Range("A1").Value  _

ActiveCell.FormulaR1C1 = "=RC[-2]-" & Sheets("Sheet1").Range("B1").Value

' or

ActiveCell.FormulaR1C1 = "=RC[-2]-Sheet1!R1C2"

**jsp_1951** · 10-16-2013, 11:42 AM

Thanks Nori,

That's exectly waht I needed!

**Bilbo_Baggins** · 10-16-2013, 11:36 AM

I'm at work at the moment ( I work as an Office Integration Developer). I'll take a closer look at this when I get home at about 1:30 PM Central time.

in the mean time, I believe some, if not all of your questions, are or can be answered by looking at the VBA code in the sample workbook I uploaded last night.
Take a look at that and see what you think of it and I'll take another look at this thread when I get home.

**jsp_1951** · 10-16-2013, 11:55 AM

Thanks Bilbo,

I didn't look at you sample workbook in detail earlier, from other threads I thought I was not explaining my problem correctly. I like the use of the button update, it will be very helpful in other macros I use.

**Norie** · 10-16-2013, 11:50 AM

No problem.

One thing though, having all those references in the code might make things a bit messy,

It might be an idea to stick the values in variables, perhaps even an array, at the start of the code.

Here's an example of how you might use an array, I've used mostly hard-coded value for now.

Dim arrSaveCols As Variant
Dim Res As Variant

    arrSaveCols = Array("CutOff", "TIME", "LPSD", ... , Sheets("INFO").Range("A11").Value ' ? arrSaveCols = Sheets("INFO").Range("A1:A11").Value

    For i = Cells(1, Columns.Count).End(xlToLeft).Column To 1 Step -1
        Res = Application.Match(Cells(1,i), arrSaveCols, 0)
        If IsError(Res) Then
            Columns(i).Delete
        End If
    Next i

**Bilbo_Baggins** · 10-16-2013, 06:42 PM

ok, I've had some time to re-examine your posts and code and have a few first comments and questions.

first of all, in your code posted today, there is a line:

Cells.Select

This code selects all cells on a worksheet. ALL CELLS
For the XLS format, this means 16,777,216 cells
For the XLSX, XLSB, and XLSM format, this means 17,179,869,184 cells
Some quick math will tell you that the newer format supports 1,024 times MORE CELLS.

Others may disagree, but I can share with you first hand, this can be VERY DANGEROUS to successful completion of your code.
Naturally, this does depend on how much actual content lives in the sheet, it's format(s), its functions and calculation state and many other factors.
I can personally attest that I have seen hundreds of developers use this step in XLS format and then attempt to run the code in the newer applications ans watch Excel crash at worst, or error out at best.

If you, using this small example as an example, get the mistaken impression you can use it in any scenario, you may find out the hard way you can't.
Please don't misunderstand, I fully expect, respect, and accept, you're using it because it may well be that is what someone showed you, or it was contained in some other code you copied.
None the less, in a future post, I'll provide an example of how you can gather the necessary information to avoid using it.

Moving on:
The For/Next Loop is ok, but it does nothing to delete any unnecessary columns that may have the misfortune to be placed to the right of the last listed column in the For/Next Loop.
Here too, I'll work into an example workbook the ability to correct this situation.

The code seems to make the assumption that the far left column will always receive the "Cut Off" heading (hard coded) but the code also seems to imply the data pasted from LAS Load may actually have a different heading.
Are these correct assumptions?

Repetitive Programmatic Copy/Pasting can cause problems with the Office Clipboard.
There is no way to programmatically empty the Office Clipboard, and it only empties itself when the last current instance of an Office application is closed.
While I certainly understand the use of copy/paste when performing steps manually, things can get carried away when code is used to do the same thing.
Frequently, I've seen people record a macro to make repetitive tasks easier, but when that repetitive task is copy/paste, I've also seen Excel crash and even adversely impact Windows and the kernel.
Granted, that can be an extreme result, but it does happen.
As an alternative, one can use some database functionality to create a "connection" to data and then append the data to another location in the workbook without ever using copy/paste.
As extra benefits:
You can have the option to select only those columns you really want from the original records,
Rename any of the columns as you specify
Avoid additional steps to delete unneeded columns
Can re-specify new or different columns on subsequent runs of data.
If desired, you can also filter data based on criteria you can specify.
All of these parameters can be externally set by you very simply by placing values in cells in a parameters sheet.
while this may seem like fairly advanced programming, it really is not that difficult.
If that opportunity seems appealing to you, I could also post a sample of that.

**jsp_1951** · 10-16-2013, 10:31 PM

I appreciate your comments, and I am very aware of how inefficient and how sensitive my code is. In addition to hard coded names and values that often need to be changed, by recording macros the code often points to specific column or cell locations rather than to a name or type of data. If anything is in the wrong place failure is certain.

Your assumptions are correct, the Cutoff channel is the result of an IF statement, operating on other columns, that populates the column with 0/1 values. Then another macro deletes all rows with a 1. Like I said, if the cuttof channel is not in col 1, the Cutoof macro won't find it.

Selecting the columns is a simple matter and a very small part of the operation. I'm sure you'd have a good laugh at how ugly the total code is, but it works. I can use it because I built it and know what breaks it, after I complete the functionality I need to make it useable for others.

**Bilbo_Baggins** · 10-16-2013, 06:45 PM

oh, one other question I forgot.

You mention the desire to change columns you want to keep.
Is this because the columns actually change from run to run, or because sometimes you may decide different columns are needed even though original columns may still be included in the new data?

**Norie** · 10-16-2013, 07:08 PM

Bilbo

Are we looking at the same code?

String and Value workbook reference

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

String and Value workbook reference

Re: String and Value workbook reference

Re: String and Value workbook reference

Re: String and Value workbook reference

Re: String and Value workbook reference

Re: String and Value workbook reference

Re: String and Value workbook reference

Re: String and Value workbook reference

Re: String and Value workbook reference

Re: String and Value workbook reference

Re: String and Value workbook reference

Re: String and Value workbook reference

Re: String and Value workbook reference

Re: String and Value workbook reference

Re: String and Value workbook reference

Re: String and Value workbook reference

Re: String and Value workbook reference

Thread Information

Users Browsing this Thread

Similar Threads

Reference cell Date and populate cells on one workbook from another workbook

[SOLVED] copy active sheet, create new workbook, name new workbook based on cell reference

Nested Cell Reference within External Workbook Reference

use a string to reference a sheet

Column Reference-How do I reference a cell in a different workbook?

Bookmarks

Bookmarks

Posting Permissions