帮助变量范围 [英] Help with variable range

问题描述

我试图找到如何使范围与文本框的列匹配。因此，如果textbox1.text =" abc"，则单元格D1 =" abc"那么我希望范围是D列，或者列可能是什么。工作表将保持不变，只是范围将改变。

I am trying to find how to make the range match the column of the text box. So if textbox1.text = "abc", and cell D1 = "abc" then I want the range to be column D, or what ever the column might be. The sheet will remain the same always just the range will change.

Function ValRepval(ByVal repval As String) As String



Dim x As Byte

Dim StrPos As String

Dim ChrString As String

Dim NumRep As String

Dim SpcRep As String

Dim ChrRep As String

Dim RepNum As String

Dim RepSpc As String

Dim RepChr As String

Dim rfnd As Range





'*********************************************

'Set up replace strings

'*********************************************

ChrString = rgch + UCase$(rgch)

RepNum = ChrString & SpcTest

RepSpc = ChrString & NumTest

RepChr = NumTest & SpcTest



'*********************************************

'Fill replace strings with random characters

'*********************************************



'Add loop to increase randomization

Randomize

NumRep = Mid$(RepNum, Int((Len(RepNum) * Rnd) + 1), 1)

Randomize

SpcRep = Mid$(RepSpc, Int((Len(RepSpc) * Rnd) + 1), 1)

Randomize

ChrRep = Mid$(RepChr, Int((Len(RepChr) * Rnd) + 1), 1)



'*********************************************

'Search selected column for matching string

'*********************************************



If Trim(repval) <> vbNullString Then

With Sheets("DataBase").Range("A:A") 'Change range to dynamic _

range must match the selected column

            Set rfnd = .Find(What:=repval, _

                            After:=.Cells(.Cells.Count), _

                            LookIn:=xlValues, _

                            LookAt:=xlWhole, _

                            SearchOrder:=xlByRows, _

                            SearchDirection:=xlNext, _

                            MatchCase:=False)

              

  End With

   End If

   

'*********************************************

'If match is found replace 'StrPos' with one _

of the replace strings.

'*********************************************



If repval = rfnd Then

 Randomize

 'Add loop to increase randomization

x = Int((Len(repval) * Rnd) + 1)

StrPos = Mid$(repval, x, 1)



If InStr(1, NumTest, StrPos) > 0 Then

    Mid$(repval, x, 1) = NumRep

 ElseIf InStr(1, ChrString, StrPos) > 0 Then

  Mid$(repval, x, 1) = ChrRep

 ElseIf InStr(1, SpcTest, StrPos) > 0 Then

  Mid$(repval, x, 1) = SpcRep

    End If

Else

  End If



'*********************************************

'Return retval

'*********************************************



ValRepval = repval

End Function

推荐答案

这里是一个简单的入门答案。
您希望代码搜索工作表上的单元格与textbox1中包含的相同值。找到匹配值时，您需要将范围设置为包含单元格的列。
请参阅下文...

OK here is a simple answer to get started.
You want the code to search the cells on a worksheet for the same value that is contained in textbox1. When a matching value is found you want to set the range to the column that contains the cell.
See below...

Dim n As String
Dim myRange As Range

n = TextBox1.Value

    Cells.Find(What:=n, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate
        

ActiveCell.EntireColumn.Select
myRange = Selection

让我知道这对你有什么用以及你正在尝试用它做什么......

这篇关于帮助变量范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！