Pass Array into Named Range and use in Vlookup

[ififthelement]

Hi,

I have an external sheet (FILE-2) where i have Tabular Data for a Test cycle. There is a Stats file (FILE-1) in which I read from the External Sheet and pass a Sheet Range (2-D Array) into Named Range (CYCLE_DATA in FILE-2).

All the above does well.

After this, in FILE-2 when i try to use Vlookup(lookupText, CYCLE_DATA, 2, False), I get #N/A.

is there a way to resolve this ?

 

[oldbrewer]

can you look up a single variable in a 2-d array

eg lookup(A1,B1:C10,2,false) ?

 

[ififthelement]

Ya, if I use lookup(A1,B1:C10,2,false) , it works perfectly well
Actually that isnt an issue anymore, coz I am copying the content from External File to the Local file now. However the code below is giving me an error in PASTESPECIAL method

  FilePath = InputBox("Please provide the File Path")
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    Set Wkb = Workbooks.Open(FilePath)
    
    Set Wks = Wkb.Worksheets("TabularData")
    Wks.Range("B4:G56").Copy
        
    ThisWorkbook.Sheets("TrendAnalysis").Unprotect "Password"
    ThisWorkbook.Sheets("TrendAnalysis").Range("U:AU").EntireColumn.Hidden = False
[B][COLOR=#ff0000]    ThisWorkbook.Sheets("TrendAnalysis").Range("U4").PasteSpecial xlPasteValues, xlPasteSpecialOperationNone, False, False[/COLOR][/B]
    ThisWorkbook.Sheets("TrendAnalysis").Range("U:AU").EntireColumn.Hidden = True
    ThisWorkbook.Sheets("TrendAnalysis").Protect "Password"

    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

 

[oldbrewer]

you are paste specialling into a single cell U4 ?

 

[ififthelement]

But I presumed that this would not be an issue, coz anyways when we right-click we typically right-click on a single cell & select PasteSpecial. We dont usually select the equal range and then PasteSpecial.

 

[oldbrewer]

I was trying to understand what you are doing - are you pasteing single cell to single cell or single cell to range or what ?

 

[ififthelement]

Hi,

the statement

[COLOR=#333333]Wks.Range("B4:G56").Copy[/COLOR]

will copy 52 rows and then am trying to paste them from

[B][COLOR=#ff0000]Range("U4")[/COLOR][/B]

onwards. Expecting that it will paste 52 rows & B:G column IN U4 ; so it will occupy 52 rows & U-Z columns

Hopes you got that.

 

[ififthelement]

Hi,

I am trying to simplify the routine. In SHEET1, I have allocated 50 Rows & 3 columns for the definite set of data for a Bar-CHART. Say,

Col-A     Col-B     Col-C
Story1     4              0
Story2      0             5
Story3      6            8
Story4      0            0
and so on......

Out of the above data I want to plot only those stories which have value greater than 0 in Col B & C , both. Such that STORY4 will not appear, rest others will

I am using the code below in a worksheet, SHEET1:

Dim AryData()

Function FindChartRange() As Variant
Dim ctr As Integer


ctr = 0
    
    For indx = 2 To 51
        If Range("B" & indx) + Range("C" & indx) > 0 Then
            ReDim Preserve AryData(2, 2, ctr)
            AryData(0, 0, ctr) = Range("A" & indx)
            AryData(1, 1, ctr) = Range("B" & indx)
            AryData(2, 2, ctr) = Range("C" & indx)
            ctr = ctr + 1
        End If
    Next
    
    FindChartRange= AryData
End Function


Sub Button2_Click()
    ActiveSheet.ChartObjects("chart 1").Activate
    ActiveChart.SetSourceData Source:=ThisWorkbook.Sheets("Sheet1").Range("chartRange")
End Sub


' where Range("chartRange")  - is the NAMED RANGE which is supposed to hold the Array; defined with WORKBOOK scope and instead of sheet range I am calling  =FindChartRange()

so that when I click the Button2, it will update the chart with only those stories that have values in Col B & C greater than 0


Problem: (1) NAMED-RANGE is not holding the array. (2) because of which the source data is not accepting NAMED-RANGE

 

[oldbrewer]

[TABLE="width: 448"]
<colgroup><col span="7"></colgroup><tbody>[TR]
[TD]Story1[/TD]
[TD]4[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Story2[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Story3[/TD]
[TD]6[/TD]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Story4[/TD]
[TD]0[/TD]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 5"]easy to write a macro that deletes rows with zero[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]in both col B and col C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Story1[/TD]
[TD]4[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Story3[/TD]
[TD]6[/TD]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Story4[/TD]
[TD]0[/TD]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 4"]if you want to keep story 2 in your data[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]macro can make this table[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]lower down or on a new sheet[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[ififthelement]

Hi,

Nopes, I cant delete rows as other column have different data. It is one sheet where I am holding a lot of intermediate/processed data . I cant restructure it on the fly.

Thats the reason I want to hold the non-zero rows in ARRAY and pass it directly to the CHART-SOURCE or via NAMED-RANGE.