VB Code HELP......

S

sezuh

Well-known Member
Joined
Nov 19, 2010
Messages
708
Hi,
I've Got this vb code which is superb,but what change do i have to make or modified so it does not assign letter like in Column"B" and "D" and also
its run without any error if the list have max rows(1048576).
thanks,i realy appreciate for any help.
Code: 
Option Explicit
Sub GradeFromAtoWhat()
    Dim RowNo As Long
    
    Do While Left(WorksheetFunction.Trim(Cells(RowNo + 1, 1)), InStr(1, WorksheetFunction.Trim(Cells(RowNo + 1, 1)), " ")) * 1 = 1
        RowNo = RowNo + 1
    Loop
    
    GradeAtoWhatever Chr(RowNo + 64)
End Sub
Sub GradeAtoWhatever(strFinalLetter As String)
    Dim arrTemp As Variant, arrBoolean As Variant
    Dim LastRow As Long, RowNo As Long, LastCol As Long, ColNo As Long, IndexNo As Long
    On Error GoTo ResetApplication
    Application.ScreenUpdating = False
    
    strFinalLetter = UCase(strFinalLetter)
    For IndexNo = 3 To (Asc(strFinalLetter) - 62)
        Cells(1, IndexNo) = Chr(IndexNo + 62)
    Next
    ReDim arrBoolean(Asc(strFinalLetter) - 65)
    
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    For RowNo = 1 To LastRow
        arrTemp = Split(WorksheetFunction.Trim(Range("A" & RowNo)))
        For IndexNo = 0 To UBound(arrBoolean)
            arrBoolean(IndexNo) = IsNumberInRange(Columns(IndexNo + 3), arrTemp)
        Next
        
        For IndexNo = 0 To UBound(arrBoolean)
            If Not arrBoolean(IndexNo) Then
                AppendToFoundList IndexNo + 3, arrTemp
                Range("B" & RowNo) = Cells(1, IndexNo + 3)
                Exit For
            End If
        Next
    Next
    
    LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
    RowNo = 0
    For ColNo = 3 To LastCol
        LastRow = Cells(Rows.Count, ColNo).End(xlUp).Row
        If LastRow > RowNo Then RowNo = LastRow
    Next
    Range(Cells(1, 3), Cells(RowNo, LastCol)).Copy
    Cells(1, LastCol + 2).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
                             SkipBlanks:=False, Transpose:=True
    Application.CutCopyMode = False
    Range(Columns(3), Columns(LastCol)).EntireColumn.Delete
    Range(Columns(3), Columns(ActiveSheet.UsedRange.Columns.Count)).ColumnWidth = 4
    Cells(1, 1).Select
    
ResetApplication:
    Err.Clear
    On Error GoTo 0
    Application.ScreenUpdating = True
End Sub
Sub AppendToFoundList(ColNo As Long, arrApend As Variant)
    Dim NextRow As Long, EndRow As Long
    
    NextRow = Cells(Rows.Count, ColNo).End(xlUp).Row + 1
    EndRow = NextRow + UBound(arrApend)
    Range(Cells(NextRow, ColNo), Cells(EndRow, ColNo)).Value = WorksheetFunction.Transpose(arrApend)
    
    Range(Cells(1, ColNo), Cells(EndRow, ColNo)).RemoveDuplicates Columns:=1, Header:=xlYes
    
End Sub
Function IsNumberInRange(rng As Range, ArrNos As Variant) As Boolean
    Dim n As Long
    Dim rngCheck As Range
    
    IsNumberInRange = False
    For n = 0 To UBound(ArrNos)
        Set rngCheck = rng.Find(ArrNos(n) * 1, , , xlWhole)
        If Not rngCheck Is Nothing Then
            IsNumberInRange = True
            Exit For
        End If
    Next
    
End Function
Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 100px"><COL style="WIDTH: 33px"><COL style="WIDTH: 33px"><COL style="WIDTH: 33px"><COL style="WIDTH: 33px"><COL style="WIDTH: 33px"><COL style="WIDTH: 33px"><COL style="WIDTH: 33px"><COL style="WIDTH: 33px"><COL style="WIDTH: 33px"><COL style="WIDTH: 33px"><COL style="WIDTH: 33px"><COL style="WIDTH: 33px"><COL style="WIDTH: 33px"><COL style="WIDTH: 33px"><COL style="WIDTH: 33px"><COL style="WIDTH: 33px"><COL style="WIDTH: 33px"><COL style="WIDTH: 33px"><COL style="WIDTH: 33px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD><TD>L</TD><TD>M</TD><TD>N</TD><TD>O</TD><TD>P</TD><TD>Q</TD><TD>R</TD><TD>S</TD><TD>T</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial Unicode MS">1 2 6 13</TD><TD>A</TD><TD> </TD><TD>A</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">6</TD><TD style="TEXT-ALIGN: right">13</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right">10</TD><TD style="TEXT-ALIGN: right">15</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">7</TD><TD style="TEXT-ALIGN: right">11</TD><TD style="TEXT-ALIGN: right">14</TD><TD style="TEXT-ALIGN: right">8</TD><TD style="TEXT-ALIGN: right">9</TD><TD style="TEXT-ALIGN: right">12</TD><TD style="TEXT-ALIGN: right">16</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial Unicode MS">1 3 12 14</TD><TD>B</TD><TD> </TD><TD>B</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">12</TD><TD style="TEXT-ALIGN: right">14</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right">11</TD><TD style="TEXT-ALIGN: right">16</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">9</TD><TD style="TEXT-ALIGN: right">13</TD><TD style="TEXT-ALIGN: right">15</TD><TD style="TEXT-ALIGN: right">6</TD><TD style="TEXT-ALIGN: right">7</TD><TD style="TEXT-ALIGN: right">8</TD><TD style="TEXT-ALIGN: right">10</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial Unicode MS">1 4 5 8</TD><TD>C</TD><TD> </TD><TD>C</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right">8</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">7</TD><TD style="TEXT-ALIGN: right">9</TD><TD style="TEXT-ALIGN: right">6</TD><TD style="TEXT-ALIGN: right">11</TD><TD style="TEXT-ALIGN: right">12</TD><TD style="TEXT-ALIGN: right">15</TD><TD style="TEXT-ALIGN: right">10</TD><TD style="TEXT-ALIGN: right">13</TD><TD style="TEXT-ALIGN: right">14</TD><TD style="TEXT-ALIGN: right">16</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial Unicode MS">1 7 15 16</TD><TD>D</TD><TD> </TD><TD>D</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">7</TD><TD style="TEXT-ALIGN: right">15</TD><TD style="TEXT-ALIGN: right">16</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">10</TD><TD style="TEXT-ALIGN: right">12</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">8</TD><TD style="TEXT-ALIGN: right">11</TD><TD style="TEXT-ALIGN: right">13</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right">6</TD><TD style="TEXT-ALIGN: right">9</TD><TD style="TEXT-ALIGN: right">14</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial Unicode MS">1 9 10 11</TD><TD>E</TD><TD> </TD><TD>E</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">9</TD><TD style="TEXT-ALIGN: right">10</TD><TD style="TEXT-ALIGN: right">11</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">8</TD><TD style="TEXT-ALIGN: right">14</TD><TD style="TEXT-ALIGN: right">15</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">6</TD><TD style="TEXT-ALIGN: right">16</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right">7</TD><TD style="TEXT-ALIGN: right">12</TD><TD style="TEXT-ALIGN: right">13</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial Unicode MS">2 3 7 9</TD><TD>C</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial Unicode MS">2 4 10 12</TD><TD>D</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial Unicode MS">2 5 11 16</TD><TD>B</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial Unicode MS">2 8 14 15</TD><TD>E</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial Unicode MS">3 4 6 16</TD><TD>E</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial Unicode MS">3 5 10 15</TD><TD>A</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial Unicode MS">3 8 11 13</TD><TD>D</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial Unicode MS">4 7 11 14</TD><TD>A</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial Unicode MS">4 9 13 15</TD><TD>B</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial Unicode MS">5 6 9 14</TD><TD>D</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial Unicode MS">5 7 12 13</TD><TD>E</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial Unicode MS">6 7 8 10</TD><TD>B</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial Unicode MS">6 11 12 15</TD><TD>C</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">19</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial Unicode MS">8 9 12 16</TD><TD>A</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">20</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial Unicode MS">10 13 14 16</TD><TD>C</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>
Sezuh
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Sort by date Sort by votes
Try this:

Code: 
Option Explicit
Sub GradeFromAtoWhat()
    Dim RowNo As Long
    
    Do While Left(WorksheetFunction.Trim(Cells(RowNo + 1, 1)), InStr(1, WorksheetFunction.Trim(Cells(RowNo + 1, 1)), " ")) * 1 = 1
'        MsgBox WorksheetFunction.Trim(Cells(RowNo + 1, 1))
'        MsgBox InStr(1, WorksheetFunction.Trim(Cells(RowNo + 1, 1)), " ")
'        MsgBox Left(WorksheetFunction.Trim(Cells(RowNo + 1, 1)), InStr(1, WorksheetFunction.Trim(Cells(RowNo + 1, 1)), " "))
        RowNo = RowNo + 1
    Loop
    
    GradeAtoWhatever Chr(RowNo + 64)
End Sub
Sub GradeAtoWhatever(strFinalLetter As String)
    Dim arrTemp As Variant, arrBoolean As Variant
    Dim LastRow As Long, RowNo As Long, LastCol As Long, ColNo As Long, IndexNo As Long
    On Error GoTo ResetApplication
    Application.ScreenUpdating = False
    
    MsgBox strFinalLetter
    strFinalLetter = UCase(strFinalLetter)
    For IndexNo = 3 To (Asc(strFinalLetter) - 62)
        Cells(1, IndexNo) = Chr(IndexNo + 62)
    Next
    ReDim arrBoolean(Asc(strFinalLetter) - 65)
    
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    For RowNo = 1 To LastRow
        arrTemp = Split(WorksheetFunction.Trim(Range("A" & RowNo)))
        For IndexNo = 0 To UBound(arrBoolean)
            arrBoolean(IndexNo) = IsNumberInRange(Columns(IndexNo + 3), arrTemp)
        Next
        
        For IndexNo = 0 To UBound(arrBoolean)
            If Not arrBoolean(IndexNo) Then
                AppendToFoundList IndexNo + 3, arrTemp
'                Range("B" & RowNo) = Cells(1, IndexNo + 3)
                Exit For
            End If
        Next
    Next
    
    LastCol = Cells(2, Columns.Count).End(xlToLeft).Column
    RowNo = 0
    For ColNo = 3 To LastCol
        LastRow = Cells(Rows.Count, ColNo).End(xlUp).Row
        If LastRow > RowNo Then RowNo = LastRow
    Next
    Range(Cells(1, 3), Cells(1, LastCol)).Clear
    Range(Cells(2, 3), Cells(RowNo, LastCol)).Copy
    Cells(2, LastCol + 2).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
                             SkipBlanks:=False, Transpose:=True
    Application.CutCopyMode = False
    Range(Columns(3), Columns(LastCol)).EntireColumn.Delete
    Range(Columns(3), Columns(ActiveSheet.UsedRange.Columns.Count)).ColumnWidth = 4
    Cells(1, 1).Select
    
ResetApplication:
    Err.Clear
    On Error GoTo 0
    Application.ScreenUpdating = True
End Sub
Sub AppendToFoundList(ColNo As Long, arrApend As Variant)
    Dim NextRow As Long, EndRow As Long
    
    NextRow = Cells(Rows.Count, ColNo).End(xlUp).Row + 1
    EndRow = NextRow + UBound(arrApend)
    Range(Cells(NextRow, ColNo), Cells(EndRow, ColNo)).Value = WorksheetFunction.Transpose(arrApend)
    
    Range(Cells(1, ColNo), Cells(EndRow, ColNo)).RemoveDuplicates Columns:=1, Header:=xlYes
    
End Sub
Function IsNumberInRange(rng As Range, ArrNos As Variant) As Boolean
    Dim n As Long
    Dim rngCheck As Range
    
    IsNumberInRange = False
    For n = 0 To UBound(ArrNos)
        Set rngCheck = rng.Find(ArrNos(n) * 1, , , xlWhole)
        If Not rngCheck Is Nothing Then
            IsNumberInRange = True
            Exit For
        End If
    Next
    
End Function
 
Upvote 0
Hi Btadams,thanks for your help it was ok for the same list above,but when i tried it on a list of "1240" rows its still assigned letters and instead of horizantal result it gave vertical one??
i tried with 759 rows i had "ERROR 5" any ideas why?
thanks again
sezuh
 
Upvote 0
would someone can improve BTADAMS suggestion please..
thanks and much obliged.
Sezuh
 
Upvote 0
is there anyone can shedlight on why am I getting"Invalid Procedure Call
or Argument(error 5)" when i try a list with 700 rows or over?????
thanks:confused:
 
Upvote 0
i would not mind if someone point me to different link or website
to solve this..
thanks for your help in advance.
sezuh
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
622
smozgur
smozgur
E
  • Question Question
VBA code clean up
Replies
9
Views
580
mumps
mumps
C
  • Question Question
Picture won't paste in cell overlay
Replies
0
Views
249
Cerka
C
E
  • Question Question
Adding a custom message.
Replies
1
Views
663
Micron
M
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C

Forum statistics

Threads
1,224,560
Messages
6,179,520
Members
452,923
Latest member
JackiG

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top