VBA - Excel使用文件名追加每个活动行 [英] VBA - Excel Append Every Active Row With File Name

问题描述

我是VBA的新人，所以忍受我。我想在每个活动行的最后一个单元格中附加文件名。因此，例如，填充列/列A1，B1，C1和D1，我想将文件名添加到单元格E1。文件名只能附加到活动行。我已经玩过不同的迭代，没有多少运气。以下是我到目前为止，逻辑显然不正确。任何帮助将不胜感激。谢谢！

Sub InsertFilename（）

  Dim Count1 As Long 
 Count1 = 1 
 Dim ColumnE As String 
 ColumnE =E1
 
 
虽然单元格（Count1，1）<> 
范围（ColumnE）。选择
 ActiveCell.FormulaR1C1 = _ 
= MID（CELL（filename），SEARCH（[，CELL文件名））+ 1，SEARCH（]，CELL（filename）） -  SEARCH（[，CELL（filename）） -  1）
 ColumnE = Range（ActiveCell，ActiveCell.Offset（1，0））。选择
 Count1 = Count1 + 1 
 Wend 
  

End Sub

解决方案

此代码从第一行到最后一行迭代，它通过从工作表的边缘模仿CTRL + LEFT来找到每行中的最后一列。

它不假定所有的行都具有相同的列数

  Dim LastRow As Long 
 Dim LastColumn As Long 
 Sub InsertFileName（）
应用程序。 ScreenUpdating = False 
 Dim i as Long 
 LastRow = ActiveSheet.Cells（ActiveSheet.Rows.Count，A）。End（xlUp）.Row 
 For i = 1 To LastRow 
 LastColumn = ActiveSheet.Cells（i，ActiveSheet.Columns.Count）.End（xlToLeft）.Column 
 Ac tiveSheet.Cells（i，LastColumn + 1）== CELL（filename）
 Next i 
 Application.ScreenUpdating = True 
 End Sub 
  

I am new to VBA so bear with me. I would like to append in the last cell of each active row the filename. So for example Row/Column A1, B1, C1, and D1 are populated I would like to add the filename to cell E1. The filename should only be appended to active rows. I have played around with different iterations without much luck. Below is what I have so far and the logic is clearly incorrect. Any help would be appreciated. Thanks!

Sub InsertFilename()

Dim Count1 As Long
Count1 = 1
Dim ColumnE As String
ColumnE = "E1"


While Cells(Count1, 1) <> ""
Range(ColumnE).Select
ActiveCell.FormulaR1C1 = _
    "=MID(CELL(""filename""),SEARCH(""["",CELL(""filename""))+1, SEARCH(""]"",CELL(""filename""))-SEARCH(""["",CELL(""filename""))-1)"
ColumnE = Range(ActiveCell, ActiveCell.Offset(1, 0)).Select
Count1 = Count1 + 1
Wend

End Sub

解决方案

This code iterates from the first row to the last, and it finds the last column in each row by imitating the CTRL+LEFT from the edge of the sheet.

It does not assume that all the rows have the same number of columns

Dim LastRow As Long
Dim LastColumn As Long
Sub InsertFileName()
  Application.ScreenUpdating = False
  Dim i as Long
  LastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row
  For i=1 To LastRow
    LastColumn = ActiveSheet.Cells(i, ActiveSheet.Columns.Count).End(xlToLeft).Column
    ActiveSheet.Cells(i,LastColumn+1)="=CELL(""filename"")"
  Next i
  Application.ScreenUpdating = True
End Sub

这篇关于VBA - Excel使用文件名追加每个活动行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！