how to use hlookup in Excel VBA with a range of data from another sheet?

I want to use the HLookUp Function, but the range is from another sheet and I can't get it right.

我想使用HLookUp函数,但范围来自另一张表,我无法正确使用它。

Sub P()

Dim mes As String
Dim i As Integer
Dim valor As Double
Dim bobes As Range
bobes = Worksheets("Cash Cost EP").Range("C3:S10")

i = 4
Do Until Cells(2, i) = ""
    mes = Cells(2, i)
    mes = Right(Cells(2, i), 2)
    If mes = "01" Then
        mes = "Enero"
    ElseIf mes = "02" Then
        mes = "Febrero"
    ElseIf mes = "03" Then
        mes = "Marzo"
    ElseIf mes = "04" Then
        mes = "Abril"
    ElseIf mes = "05" Then
        mes = "Mayo"
    ElseIf mes = "06" Then
        mes = "Junio"
    ElseIf mes = "07" Then
        mes = "Julio"
    ElseIf mes = "08" Then
        mes = "Agosto"
    ElseIf mes = "09" Then
        mes = "Setiembre"
    ElseIf mes = "10" Then
        mes = "Octubre"
    ElseIf mes = "11" Then
        mes = "Noviembre"
    ElseIf mes = "12" Then
        mes = Diciembre
    End If
    Sheets("resumen").Range("d17").Value = "= HLookup(mes, bobes, 3, 0)"
    i = i + 1
Loop

End Sub

When I run the code, it doesn't work the way I want it to.
Pleas help, I want to get the value of the Hlookup in cell D17.

当我运行代码时,它不能按照我想要的方式工作。请求帮助,我想在单元格D17中获得Hlookup的价值。

1 个解决方案

#1

It is a little unclear as 'doesn't work the way I want it to' is a little vague as to what the problem is. I would guess that you need to change the line below to:

有点不清楚,“不按照我想要的方式工作”对于问题是什么有点模糊。我猜你需要将下面的行更改为:

Sheets("resumen").Range("d17").Value = "=HLOOKUP(" & Chr(34) & mes & Chr(34) _ & ",'" & bobes.Parent.Name & "'!" & bobes.Address & ",3,0)"

表格(“resumen”)。范围(“d17”)。值=“= HLOOKUP(”&Chr(34)&mes&Chr(34)_&“,'”&bobes.Parent.Name&“'!” &bobes.Address&“,3,0)”

So that you pass the value of mes (including double quotes) rather than the text 'mes' to the function and also identify the worksheet and range address of the bobes range.

这样你就可以将mes(包括双引号)的值而不是文本'mes'传递给函数,还可以识别bobes范围的工作表和范围地址。

#1