CODEWITHSUNDEEP
vbaexcel
KD Gordon
KD Gordon

Reputation: 5

Converting Excel Formula to VBA and use Array

I need to convert the following excel formula to VBA code:

=C10+H:H

UPDATE: I want to take the values in column H of Worksheet1 and add them to the value in cell C10. I then want to compare that new value to a range of values in column C in Worksheet 2. I have been using the entire column because the rows that use the data in that column fluctuate.

I want to be able to look at the data a decade at a time. So at 10 years I am using Worksheet1!H20:H30. Then at 20 years I would use Worksheet1!H20:H40 and so on.

I could reasonably for VBA purposes limit the range from the entire column to Worksheet1!H20:H1000 (maybe even less, not sure just yet). The data in column H is what I need to put in an array and then need to be able to add to that data (will probably need to store result in second array) and then compare the result to an additional grouping of data on a separate worksheet (i.e. Worksheet2!C:C).

I need to calculate age based on annual incremental increases. I have my starting age stored in C10. I have the annual increments stored in column H. The way it works in excel is if I have this in cell B22 it add C10 to H22.

This formula is wrapped in a long and daunting if statement. It compares the calculated age to an age range on a different worksheet in lets say column C (i.e. Worksheet2!C:C = C10 + Worksheet1!H:H).

I need to be able to do that in VBA.

I tried in VBA ElseIf Worsheets("Sheet2").Range("C:C") = C10 + Worsheets("Sheet1").Range("H:H")

It throw a match error.

UPDATE: FOR LOOP IN FUNCTION I TRIED TO USE

Function arrCalc_30yrMax_age_annual_incr_stnd_mbr() As Variant

    'SETS THE VARIABLES
    Dim wb As Workbook
    Dim ws_Calculator_TL_30yrMaxTerm As Worksheet
    Dim ws_LVRates As Worksheet
    Dim ws_TLRates As Worksheet
    Dim ws_Misc As Worksheet
    Dim rngCalc_30yrMax_age_mbr As Range
    Dim rngCalc_30yrMax_age_sp As Range
    Dim rngCalc_30yrMax_age_annual_incr As Range
    Dim arrCalc_30yrMax_age_annual_incr As Variant
    Dim arrAge_Annual_Inc As Variant
    Dim Row As Long
    Dim Column As Long

    'SETS RANGE FOR rngCalc_30yrMax_age_annual_incr RANGE
    Set wb = ThisWorkbook
    Set ws_Calculator_TL_30yrMaxTerm = Worksheets("Calculator_TL_30yrMaxTerm")
    Set ws_LVRates = wb.Worksheets("LVRates")
    Set ws_TLRates = wb.Worksheets("TLRates")
    Set ws_Misc = wb.Worksheets("Misc")
    Set rngCalc_30yrMax_age_annual_incr = ws_Calculator_TL_30yrMaxTerm.Range("Calc_30yrMax_age_annual_incr")

    'SETS ARRAY EQUAL TO rngCalc_30yrMax_age_annual_incr
    arrCalc_30yrMax_age_annual_incr = rngCalc_30yrMax_age_annual_incr

    'LOOP THROUGH THE ARRAY OF WORKSHEET VALUES
    For Row = 1 To UBound(arrCalc_30yrMax_age_annual_incr, 1) 'First array dimension is rows.
        For Column = 1 To UBound(arrCalc_30yrMax_age_annual_incr, 1) 'Secong array dimension is columns.
            arrAge_Annual_Inc = arrCalc_30yrMax_age_annual_incr + rngCalc_30yrMax_age_mbr
        Next Column
    Next Row



End Function

Upvotes: 0

Views: 374

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60174

Absent data and desired results, I remain uncertain as to just what you want. Perhaps this approach will help, if my assumptions are correct. If not, I'll delete it.

I think what you are trying to do (based on your ELSEIF statement above)

  • examine each value in Column C
  • find the value in Column H that is equal to that value +10
  • if you find such a value, do something.
  • if your comparison is more complicated (eg finding an inexact match, and the values in column H are sorted, a different approach should be used)

Here is one way of doing it. (and there are a number of others, in addition to looping through all the data -- exactly which is best (fastest) depends on the circumstances.

Option Explicit

'Worsheets("Sheet2").Range("C:C") = C10 + Worsheets("Sheet1").Range("H:H")

Sub Marine()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim r1 As Range, r2 As Range
    Dim vToFind 'declare as a type of value ?date  ?long ?double
    Dim c1 As Range, c2 As Range

Set ws1 = Worksheets("sheet1")
Set ws2 = Worksheets("sheet2")

'set ranges to end at the last used cell in the columns
With ws1
    Set r1 = .Range(.Cells(1, "C"), .Cells(.Rows.Count, "C").End(xlUp))
End With
With ws2
    Set r2 = .Range(.Cells(1, "H"), .Cells(.Rows.Count, "H").End(xlUp))
End With

For Each c1 In r1
    vToFind = c1 - Range("c10")
        Set c2 = r2.Find(what:=vToFind, after:=r2(1), _
                        LookIn:=xlValues, searchdirection:=xlNext)
        If Not c2 Is Nothing Then
            'do your thing
            Stop
        Else
            MsgBox vToFind & " not found in target"
        End If
Next c1

End Sub

Upvotes: 0

Related Questions