locked
Subtracting a single value from an array RRS feed

  • Question

  • Hi again:
    l have been searching the net for the last couple of days for an answer and although there are multiple hits.. none seems to be answer my question...l have an array of data that need to have a single value subtracted from it.. which is in a named cell... l get a mismatched error when l run the sub..l have attempt to set the cell value to a constant and then subtract the value form the array..l which then gets put into another array with a differenet name... think the problem is that although the data name is an array, the value subtract is a single value, and so when l try to subtract it from the array there is only one value to subtract and not an array of values.. hope that makes sense..what l am attempting to do is set the single value cell to an absolute reference within the for loop for that particular loop, but then to change to the next cell value for the next data column..
    maybe the code will make more sense..

    or i = 1 To 16

        Selection.Resize(Selection.Rows.Count + 2047, Selection.Columns.Count).Select
       
        Offset1 := INDIRECT("AvgeChanVal" & i & "")
       
        Selection.Formula = "{= Data!Data" & i & ")" - Offset1
       
        'Selection.Formula = "{= Data!Data" & i & ")" - ("AvgeChanVal" & i & "}")  this is my orginal formula which keeps getting the mismatch error
          
        With Selection
            .NumberFormat = "0.000"
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlBottom
            .WrapText = False
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = False
            .Name = "SmoothedData" & i & ""
        End With
        ActiveCell.Offset(0, 1).Select

    its probably just a operator that l do not know about... oh that brings me to my next question... could u please point me toewards a good refernce book for Excel.. that contains all thiose liittle quirks of programming...

    TIA

    ben196414
    • Edited by ben196414 Monday, March 30, 2009 2:15 PM spelling was atrocious
    Monday, March 30, 2009 2:12 PM

Answers

  • Hi:

    I worked out a solution for my problem using the cell address property..as that gives an absolute cell reference l was able to use property to subtract the value from the array, and return an array.
    • Marked as answer by ben196414 Wednesday, April 1, 2009 12:45 PM
    Wednesday, April 1, 2009 12:45 PM

All replies

  • Hi:

    I worked out a solution for my problem using the cell address property..as that gives an absolute cell reference l was able to use property to subtract the value from the array, and return an array.
    • Marked as answer by ben196414 Wednesday, April 1, 2009 12:45 PM
    Wednesday, April 1, 2009 12:45 PM
  • Could you give a code example of your solution? can't figure it out...
    Thursday, October 13, 2011 1:05 PM