如果有数据，是否有一种方法可以循环遍历范围并返回旁边单元格中的公式？

发布于 2025-02-13 20:23:03 字数 724 浏览 3 评论 0原文

我一直在通过Google和Stack Overflow进行研究，以查看是否可以找到类似的问题，但是我还没有看到它。

我试图告诉Excel查看数据集中的整个范围（整个列F）（数据集量经常更改）。此列由供应商编号或空白生成。我需要循环遍历整个列和每个单元格，然后写一个逻辑语句，如果列F列中的特定单元格中有一个供应商号，请在同一行中返回G列中的V lookup，该列在f列中查找供应商号码。因此，例如，如果F13是空白的，则我需要在每个单元格中插入Cell G13“ = Q13”，等等。因此，如果F14为空白，则G14必须为“ Q14”。而且，如果F20中有一个供应商号码，我需要返回G20中的Vlookup，例如“ = vlookup（f20，映射！$ a $ a $ 4：$ b $ 1000,2，false）”。

这是我目前到目前为止的目前，但是我真的很努力地努力循环并告诉Excel指向同一行，但代码中的列不同。

Dim LastRow As Long
LastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "F").End(xlUp).Row

Dim CL As Range
Dim Rng As Range
Set Rng = Worksheets("Sheet1").Range("LastRow")
For Each CL In Rng
If CL.Value = "" Then
    CL.Value = "=VLOOKUP("

如果我不在正确的轨道上，请感谢任何帮助。

原文

I have been researching through google and stack overflow to see if I can find a similar problem, but I haven't seen it yet.

I am trying to tell excel to look at an entire range (entire column F) within my dataset (The dataset amount changes often). This column gets generated with Vendor numbers or blanks. I need to loop through the entire column and each cell and write a logical statement where If there is a vendor number in the specific cell within Column F, return a VLOOKUP in Column G in the same row that looks up the vendor number in column F. And if there is no data in column F in a particular cell, I need it to insert an equals sign and point to column Q but in the same Row. So for example, if F13 is Blank, I need to insert in cell G13 "=Q13" and so on for each cell. So if F14 is Blank, G14 would have to be "Q14". And if there is a vendor number in F20, I would need to return a VLOOKUP in G20 such as "=VLOOKUP(F20,Mapping!$A$4:$B$1000,2,FALSE)".

This is what I currently have so far, but I am really struggling on how to loop through and tell excel to point to the same row but different column within the code.

Dim LastRow As Long
LastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "F").End(xlUp).Row

Dim CL As Range
Dim Rng As Range
Set Rng = Worksheets("Sheet1").Range("LastRow")
For Each CL In Rng
If CL.Value = "" Then
    CL.Value = "=VLOOKUP("

If I am not on the right track, any help is appreciated.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

泪是无色的血 2025-02-20 20:23:03

这样的东西？

Dim ws As Worksheet
Dim rc As Long, i As Long

Set ws = ActiveSheet
rc = ws.Range("F" & Rows.Count).End(xlUp).Row
For i = 1 To rc
    If ws.Range("F" & i) <> "" Or Not IsEmpty(ws.Range("F" & i).Value) Then
        ws.Range("G" & i).Formula = "=VLOOKUP(F" & i & ",Mapping!$A$4:$B$1000,2,FALSE)"
    Else
        ws.Range("G" & i).Value = ws.Range("Q" & i).Value
    End If
Next

Something like this?

Dim ws As Worksheet
Dim rc As Long, i As Long

Set ws = ActiveSheet
rc = ws.Range("F" & Rows.Count).End(xlUp).Row
For i = 1 To rc
    If ws.Range("F" & i) <> "" Or Not IsEmpty(ws.Range("F" & i).Value) Then
        ws.Range("G" & i).Formula = "=VLOOKUP(F" & i & ",Mapping!$A$4:$B$1000,2,FALSE)"
    Else
        ws.Range("G" & i).Value = ws.Range("Q" & i).Value
    End If
Next

回复收藏 0 原文

~没有更多了~