Need Macro restructered please help
Sub mySearch()
For Each gCell In Range("G1", Range("G65536").End(xlUp))
If IsEmpty(Cells(gCell.Row, 8)) Then
For Each c In Range("Z1:AOO324597")
I = Application.Match(gCell.Value, Range(c, Cells(324597, c.Column)), 0)
If Not IsError(I) Then
Cells(gCell.Row, 8) = I
Exit For
End If
Next
If IsEmpty(Cells(gCell.Row, 8)) Then
Cells(gCell.Row, 8) = "#N/A"
End If
End If
Next
End Sub
I need the above code to be changed, for example instead of this code searching for data in the range Z1 to AOO324597; I want it to be able search in Column Z1 to 324597 instead.
if any body can help it would much appreciated! Thank you
cross posted
cross posted at
http://www.mrexcel.com/forum/excel-questions/885361-visual-basic-applica...
Original code is wasteful
Original code is wasteful searching the same (very similar) areas 324,000 times when nothing's found!
However, for your current requirements:
Sub mySearch2()For Each gcell In Range("G1", Range("G65536").End(xlUp)).Offset(, 1).Cells
If IsEmpty(gcell) Then
I = Application.Match(gcell.Offset(, -1).Value, Range("Z1:Z324597"), 0)
If IsError(I) Then gcell.Value = "#N/A" Else gcell.Value = I
End If
Next
End Sub
Vba getting error need correcting code
Sub translate_BB_BF_output_EO_EZ() Application.ScreenUpdating = False
firstRow = Range("BA1").End(xlDown).Row lastRow = Cells(Rows.Count, "BA").End(xlUp).Row bbColumn = Range("BB1").Column
eoColumn = Range("EO1").Column
Range("EZ" & firstRow - 2, "FU" & firstRow - 1).Clear
Set outputRange = Range("EO" & firstRow - 2, "FU" & lastRow) outputRange.HorizontalAlignment = xlCenter
For i = 0 To 20
Cells(firstRow - 2, Range("FA1").Column + i) = Chr(65 + i) + "" Next
Range("EZ" & (firstRow - 1)).Value = "colorCode"
' for each combination in column bb to bf For r = firstRow To lastRow
If (Range("EN" & r).Value = "") Then
Dim myArray(1 To 35) As Integer 'assuming maximum value is 35 For i = 1 To 35
myArray(i) = i Next
'value themselves can't be neighbors, set them to 0
For c = 0 To 4
num = Cells(r, bbColumn + c).Value myArray(num) = 0
Next
'find nearest neighbors in myArray and set it to 0 For c = 0 To 4
num = Cells(r, bbColumn + c).Value neighborCount = 0
requiredCount = 3
If c = 4 Then
requiredCount = 4 End If
shift = 1
While neighborCount < requiredCount
'search left in myArray
If (num - shift) >= 1 And (num - shift) <= 35 Then
If myArray(num - shift) <> 0 Then myArray(num - shift) = 0 neighborCount = neighborCount + 1
End If End If
'search right in myArray
If neighborCount < requiredCount And (num + shift) >= 1 And (num + shift) <= 35 Then
If myArray(num + shift) <> 0 Then
myArray(num + shift) = 0
neighborCount = neighborCount + 1 End If
End If
shift = shift + 1 Wend
Next
'out neighbors' table
Dim alphaArray(1 To 35) As String indx = 0
For i = 1 To 35
If myArray(i) = 0 Then
alphaArray(i) = Chr(65 + indx) + "" Cells(r, Range("FA1").Column + indx) = i indx = indx + 1
End If Next
'output translated values For c = 0 To 4
num = Cells(r, bbColumn + c).Value
Cells(r, eoColumn + c) = num
Cells(r, eoColumn + 6 + c) = alphaArray(num)
Next
changeRowColor roww:=r Range("EN" & r).Value = "DONE"
End If Next
Application.ScreenUpdating = True End Sub
Sub changeRowColor(ByVal roww As Integer) ColorIndex = 3
Dim colSig As String
colCode = ""
Range("FA" & roww).Interior.ColorIndex = ColorIndex colFreq = 1
For Each cel In Range("FB" & roww, "FU" & roww)
If cel.Value - cel.Offset(0, -1).Value > 1 Then colCode = colCode & " " & colFreq colFreq = 0
ColorIndex = ColorIndex + 1
Select Case ColorIndex Case 5, 9, 11, 21
ColorIndex = ColorIndex + 1 End Select
End If
cel.Interior.ColorIndex = ColorIndex
colFreq = colFreq + 1 Next cel
colCode = colCode & " " & colFreq
Range("EZ" & roww).Value = Trim(colCode) End Sub
So I need corrected the (changerowcolor roww:=r)
I believe it's cause I added more data and may need to change this to accept the more data to run. Let me know if you can help get this running right again. Much Thanks
P.s. Code is spaced but it is one entire code.
It's fixed it, I placed as
It's fixed it, I placed as long in correct place and it works. Good job! thanks! I use the code for glucose chemistry monitoring. Thanks again!
It says it an over flow error
It says it an over flow error
What is it supposed to be
What is it supposed to be doing?!
Try changing:
Sub changeRowColor(ByVal roww As Integer)
to:
Sub changeRowColor(ByVal roww As Long)
I looked at code it is
I looked at code it is allready changed to as long when I hit debug it goes to the changerowcolor roww:=r which leads me to believe thesis the problem
What's the value of r at that
What's the value of r at that time?
It's almost impossible to help you further without seeing the file.
VBA to count rows between dates
More info regarding counting rows between dates.
The data is in one column, column FZ . The date is in column GA. I would like the the two dates it is looking at placed in GB ans the count of the rows found in between in column GC. Any help would be appreciated. Thank you !
Need this to be in VBA. Thank
Need this to be in VBA. Thank you