Find Method w/ VALUE error

[Aaron719]

I'm trying to use the code below to search a range for a specific value and then replace the contents of the cell above it with a different value. I'm getting a "Value" error.


Function Ehour2(wageVal As Double, hourVal As Double) As String
With Worksheets(Incomes).Range("G14:AK14,G30:AK30,G46:AK46,G62:AK62,G78:AK78,G94:AK94,G110:AK110,G126:AK126 ,G142:AK142,G158:AK158,G174:AK174,G190:AK190")
Set w = .Find(What:=wageVal, LookIn:=xlValues, SearchOrder:=xlByRows)
If Not w Is Nothing Then
w.Address.Offset(-1, 0).Value = hourVal
End If
End With
End Function

[GTO]

Not tested, but .Address returns a string, which ain't likely helpful to .Offset, which needs the .Range to work from... (Ditch .Address)

Hope that helps,

Mark

[Bob Phillips]

If its a UDF, yoiu can't change other cells within that, only return a value to the calling cell.

[snb]

sub M_snb(c00,c01)
on error resume next

Intersect(Range("G:AK"), Range("14:14,30:30,46:46,62:62,78:78,94:94,110:110,126:126,142:142,158:158,174:174 ,190:190")).Find(c00).offset(-1)=c01
end sub

[Aussiebear]

I'm assuming its a possible typo but the sheet name needs to be in double quotes in your initial post

[GTO]

I'm assuming its a possible typo but the sheet name needs to be in double quotes in your initial post

:doh:

Hi Ted :hi:

[Aaron719]

Ha yah I guess I forgot the quotes... :)

what does c00 and c01 mean?

So I tried snb's code without changing anything, because I don't know what c00 or c01 mean. I also tried replacing that with the ranges I want to use and it didn't work. I also tried making the change GTO suggested to my original code and that didn't. I also tried combining the two codes in a variety of ways and nothing has worked yet. I'm not getting any errors so I'm wondering if its not sure what to do so its doing something and I'm not noticing what it is.

[GTO]

Can you post/attach the workbook?

[Aaron719]

Well here is my new code. I've done some research and followed your ques and I feel good about this one, except its not doing anything. Just to give you guys everything you may need; I want to search the Incomes sheet for a value (referenced on the Menu sheet) within a specified range and fill the cell directly above it with a value also from the Menu sheet and I want this to loop until the cell above is greater than 0.

Am I allowed to reference a cell from one sheet to search a range in a different sheet?


Sub Ehour3()
With Worksheets("Incomes").Range("G14:AK14,G30:AK30,G46:AK46,G62:AK62,G78:AK78,G94:AK94,G110:AK110,G126:AK126 ,G142:AK142,G158:AK158,G174:AK174,G190:AK190")
Set W = .Find(What:=Range("Menu!E4"), LookIn:=xlValues, SearchOrder:=xlByRows)
If Not W Is Nothing Then
Do
W.Offset(-1, 0).ClearContents.Value = Range("Menu!G4")
Set W = .FindNext(What:=Range("Menu!E4"), LookIn:=xlValues, SearchOrder:=xlByRows)
Loop Until W.Offset(-1) > 0
End If
End With
End Sub

[Aussiebear]

As you step through the code using F8, where does it fail?

[Aaron719]

Well... As I step through it, I hit F8 as...

W.Offset(-1, 0).ClearContents.Value = Range("Menu!G4")
is highlighted, and it pulls up a different module, called "DayName"...

Function DayName(dateVal As String, monVal As Integer, dayVal As Integer) As String


If Month(dateVal) = monVal And Day(dateVal) = dayVal Then
DayName = WeekdayName(Weekday(dateVal))
ElseIf Month(dateVal) < monVal And Day(dateVal) >= dayVal Then
DayName = WeekdayName(Weekday(DateSerial(Year(dateVal), monVal, dayVal)))
ElseIf Month(dateVal) < monVal And Day(dateVal) <= dayVal Then
DayName = WeekdayName(Weekday(DateSerial(Year(dateVal), monVal, dayVal)))
ElseIf Month(dateVal) <= monVal And Day(dateVal) < dayVal Then
DayName = WeekdayName(Weekday(DateSerial(Year(dateVal), monVal, dayVal)))
ElseIf Month(dateVal) > monVal And Day(dateVal) <= dayVal Then
DayName = WeekdayName(Weekday(DateSerial(Year(dateVal) + 1, monVal, dayVal)))
ElseIf Month(dateVal) > monVal And Day(dateVal) >= dayVal Then
DayName = WeekdayName(Weekday(DateSerial(Year(dateVal) + 1, monVal, dayVal)))
ElseIf Month(dateVal) >= monVal And Day(dateVal) > dayVal Then
DayName = WeekdayName(Weekday(DateSerial(Year(dateVal) + 1, monVal, dayVal)))
Else
DayName = "1"
End If


End Function


I cycle through DayName a 2,3,4 times (Random) and it brings up a different module called "EWage2".

EWage2 relies on DayName and my find method relies on EWage2. I don't know if this is relative to the problem or not...

I then cycle through EWage2 2, 3, 4 times (random) and it brings up DayName. This cycle just gets repeated.

Note: My find method searches through the cells that are filled with my EWage2 function.


Function EWage2(wageVal As String, intVal As String, dVal As String, xVal As String, yVal As String, dayVal As String, hVal As Double, h2Val As Double) As Double


If h2Val > 0 Then
EWage2 = wageVal
ElseIf intVal = "Daily" Then
EWage2 = wageVal
ElseIf intVal = "Weekly" Then
If Month(dVal) = yVal And Day(dVal) = xVal Then
EWage2 = wageVal
ElseIf dayVal = WeekdayName(Weekday(dVal)) Then
EWage2 = wageVal
Else:
EWage2 = 0
End If
ElseIf intVal = "Bi-Weekly" Then
If Month(dVal) = yVal And Day(dVal) = xVal Then
EWage2 = wageVal
ElseIf (Month(DateAdd("d", 14, dVal)) = yVal And Day(DateAdd("d", 14, dVal)) = xVal) Or (Month(DateAdd("d", 14 * 2, dVal)) = yVal And Day(DateAdd("d", 14 * 2, dVal)) = xVal) _
Or (Month(DateAdd("d", 14 * 3, dVal)) = yVal And Day(DateAdd("d", 14 * 3, dVal)) = xVal) Or (Month(DateAdd("d", 14 * 4, dVal)) = yVal And Day(DateAdd("d", 14 * 4, dVal)) = xVal) _
Or (Month(DateAdd("d", 14 * 5, dVal)) = yVal And Day(DateAdd("d", 14 * 5, dVal)) = xVal) Or (Month(DateAdd("d", 14 * 6, dVal)) = yVal And Day(DateAdd("d", 14 * 6, dVal)) = xVal) _
Or (Month(DateAdd("d", 14 * 7, dVal)) = yVal And Day(DateAdd("d", 14 * 7, dVal)) = xVal) Or (Month(DateAdd("d", 14 * 8, dVal)) = yVal And Day(DateAdd("d", 14 * 8, dVal)) = xVal) _
Or (Month(DateAdd("d", 14 * 9, dVal)) = yVal And Day(DateAdd("d", 14 * 9, dVal)) = xVal) Or (Month(DateAdd("d", 14 * 10, dVal)) = yVal And Day(DateAdd("d", 14 * 10, dVal)) = xVal) _
Or (Month(DateAdd("d", 14 * 11, dVal)) = yVal And Day(DateAdd("d", 14 * 11, dVal)) = xVal) Or (Month(DateAdd("d", 14 * 12, dVal)) = yVal And Day(DateAdd("d", 14 * 12, dVal)) = xVal) _
Or (Month(DateAdd("d", 14 * 13, dVal)) = yVal And Day(DateAdd("d", 14 * 13, dVal)) = xVal) Or (Month(DateAdd("d", 14 * 14, dVal)) = yVal And Day(DateAdd("d", 14 * 14, dVal)) = xVal) _
Or (Month(DateAdd("d", 14 * 15, dVal)) = yVal And Day(DateAdd("d", 14 * 15, dVal)) = xVal) Or (Month(DateAdd("d", 14 * 16, dVal)) = yVal And Day(DateAdd("d", 14 * 16, dVal)) = xVal) _
Or (Month(DateAdd("d", 14 * 17, dVal)) = yVal And Day(DateAdd("d", 14 * 17, dVal)) = xVal) Or (Month(DateAdd("d", 14 * 18, dVal)) = yVal And Day(DateAdd("d", 14 * 18, dVal)) = xVal) _
Or (Month(DateAdd("d", 14 * 19, dVal)) = yVal And Day(DateAdd("d", 14 * 19, dVal)) = xVal) Or (Month(DateAdd("d", 14 * 20, dVal)) = yVal And Day(DateAdd("d", 14 * 20, dVal)) = xVal) _
Or (Month(DateAdd("d", 14 * 21, dVal)) = yVal And Day(DateAdd("d", 14 * 21, dVal)) = xVal) Or (Month(DateAdd("d", 14 * 22, dVal)) = yVal And Day(DateAdd("d", 14 * 22, dVal)) = xVal) _
Or (Month(DateAdd("d", 14 * 23, dVal)) = yVal And Day(DateAdd("d", 14 * 23, dVal)) = xVal) Or (Month(DateAdd("d", 14 * 24, dVal)) = yVal And Day(DateAdd("d", 14 * 24, dVal)) = xVal) _
Or (Month(DateAdd("d", 14 * 25, dVal)) = yVal And Day(DateAdd("d", 14 * 25, dVal)) = xVal) Then
EWage2 = wageVal
Else:
EWage2 = 0
End If
If EWage2 = wageVal Then
h2Val = hVal
Else:
EWage2 = 0
End If
ElseIf intVal = "Monthly" Then
If Month(dVal) = yVal And Day(dVal) = xVal Then
EWage2 = wageVal
ElseIf (Month(DateAdd("m", 1, dVal)) = yVal And Day(DateAdd("m", 1, dVal)) = xVal) Or (Month(DateAdd("m", 2, dVal)) = yVal And Day(DateAdd("m", 2, dVal)) = xVal) _
Or (Month(DateAdd("m", 3, dVal)) = yVal And Day(DateAdd("m", 3, dVal)) = xVal) Or (Month(DateAdd("m", 4, dVal)) = yVal And Day(DateAdd("m", 4, dVal)) = xVal) _
Or (Month(DateAdd("m", 5, dVal)) = yVal And Day(DateAdd("m", 5, dVal)) = xVal) Or (Month(DateAdd("m", 6, dVal)) = yVal And Day(DateAdd("m", 6, dVal)) = xVal) _
Or (Month(DateAdd("m", 7, dVal)) = yVal And Day(DateAdd("m", 7, dVal)) = xVal) Or (Month(DateAdd("m", 8, dVal)) = yVal And Day(DateAdd("m", 8, dVal)) = xVal) _
Or (Month(DateAdd("m", 9, dVal)) = yVal And Day(DateAdd("m", 9, dVal)) = xVal) Or (Month(DateAdd("m", 10, dVal)) = yVal And Day(DateAdd("m", 10, dVal)) = xVal) _
Or (Month(DateAdd("m", 11, dVal)) = yVal And Day(DateAdd("m", 11, dVal)) = xVal) Or (Month(DateAdd("m", 12, dVal)) = yVal And Day(DateAdd("m", 12, dVal)) = xVal) Then
EWage2 = wageVal
Else
EWage2 = 0
End If
Else:
EWage2 = 0
End If
End Function

[Bob Phillips]

Why don't you post the workbook, it is difficult trying to second-guess the data.

[Aaron719]

It's attached

[Aussiebear]

From the sample file you provided, Cells G4 & E4 on the Menu sheet are blank. So how do we follow the concept? I also not sure how the DayName Function can evoke another module given that it doesn't actually call the other module.

Sub Ehour3()
With Worksheets("Incomes").Range("G14:AK14,G30:AK30,G46:AK46,G62:AK62,G78:AK78,G94:AK94,G110:AK110,G126:AK126 ,G142:AK142,G158:AK158,G174:AK174,G190:AK190")
Set W = .Find(What:=Range("Menu!E4"), LookIn:=xlValues, SearchOrder:=xlByRows)
If Not W Is Nothing Then
Do
W.Offset(-1, 0).ClearContents.Value = Range("Menu!G4")
Set W = .FindNext(What:=Range("Menu!E4"), LookIn:=xlValues, SearchOrder:=xlByRows)
Loop Until W.Offset(-1) > 0
End If
End With
End Sub

Apart from that the error line that you mentioned it lets look at the logic of what this module is attempting to do. You have a series of ranges between columns G & AK that are (if I follow you code correctly) targets to search in for the value in Menu!E4. So why are trying to complete a .find for a known cell location? Why not say Set W = Range(Menu!E4)?

The next line I'm concerned with is


W.Offset(-1, 0).ClearContents.Value = Range("Menu!G4")

This works as a stand alone line

W.Offset(-1, 0).ClearContents

And since Menu!G4 is already blank the rest of the line seems un-necessary.

[Aaron719]

Looking over what you have said Aussiebear, my code does seem redundant and illogical. Too much time in front of the screen I guess. thanks for the response.

Am at work at the moment, so I'll have to make changes when I get home.

[Aaron719]

So I tried what you said and I have this


Sub Ehour3()
With Worksheets("Incomes").Range("G14:AK14,G30:AK30,G46:AK46,G62:AK62,G78:AK78,G94:AK94,G110:AK110,G126:AK126 ,G142:AK142,G158:AK158,G174:AK174,G190:AK190")
Set W = Range("Menu!E2")
If Not W Is Nothing Then
Do
W.Offset(-1, 0).Value = Range("Menu!G2")
End If
End With
End Sub


but this is just going to make the cell above "Menu!E2" as the value of "Menu!G2". I have to use .find because I want to search the range in incomes sheet for a value equal to "Menu!E2" (say $12.50) and fill the cell above the cell it found with whatever the value is in "Menu!G2" (say 8). The code above does not do that.

I feel like I'm just confusing you guys with the first code I submitted because it was obviously wrong and didn't portray what I'm trying to do. So just forget about that.

SO... my new code that I haven't written with my best knowledge of how this stuff works and is supposed to do exactly what I want, but is not. I get run time error '91' "object variable or With block variable not set"

new code and workbook below it...


Sub Ehour3()
With Worksheets("Incomes").Range("G14:AK14,G30:AK30,G46:AK46,G62:AK62,G78:AK78,G94:AK94,G110:AK110,G126:AK126 ,G142:AK142,G158:AK158,G174:AK174,G190:AK190")
Set W = .Find(What:=Range("Menu!E2"), LookIn:=xlValues, SearchOrder:=xlByRows)
Do
W.Offset(-1, 0).Value = Range("Menu!G2")


Loop Until W.Offset(-1) > 0
End With
End Sub

[Aussiebear]

I'm not sure I follow your logic here, so this is simply a stab in the dark


Sub EHour3()
With Worksheets("Incomes")
Set rTarget= Intersect(.Range ("G14:AK14,G30:AK30,G46:AK46,G62:AK62,G78:AK78,G94:AK94,G110:AK110,G126:AK126 ,G142:AK142,G158:AK158,G174:AK174,G190:AK190")
Set W =Range("Menu!E4").Value
For each cel in rTarget
If Cel.value = W.value then
W.Offset(-1,0).value = Range("Menu!G2").value
Else
Next cel
End If
End With
End Sub

[GTO]

Hi Aaron,


I'm trying to use the code below to search a range for a specific value and then replace the contents of the cell above it with a different value...

I am guessing, but given your non-contiguous range referred to and the loop you were trying, my stab is this:

You want to find each occurrence of 12.5 for a wage and tack in 15 above each cell we found 12.5 in (using your current example values), is that correct?

Presuming so, then maybe...

In a Standard Module:

Option Explicit

Sub Ehour3()
Dim rngSearchRange As Range
Dim W As Range
Dim strFirstCellAddress As String
Dim strLastFoundAddress As String

Set rngSearchRange = ThisWorkbook.Worksheets("Incomes").Range("G14:AK14,G30:AK30,G46:AK46,G62:AK62,G78:AK78,G94:AK94,G110:AK110,G126:AK126 ,G142:AK142,G158:AK158,G174:AK174,G190:AK190")
Set W = rngSearchRange.Find(What:=CStr(ThisWorkbook.Worksheets("Menu").Range("E2").Value), LookIn:=xlValues, SearchOrder:=xlByRows)

If Not W Is Nothing Then
strFirstCellAddress = W.Address(0, 0, , -1)
W.Offset(-1).Value = ThisWorkbook.Worksheets("Menu").Range("G2").Value

Do
strLastFoundAddress = W.Address(0, 0, , -1)
Set W = rngSearchRange.FindNext(W)
If Not W.Address(0, 0, , -1) = strFirstCellAddress And Not W.Address(0, 0, , -1) = strLastFoundAddress Then
W.Offset(-1).Value = ThisWorkbook.Worksheets("Menu").Range("G2").Value
End If
Loop While Not W.Address(0, 0, , -1) = strFirstCellAddress
End If
End Sub


Regardless of whether that works (well, it works, but I mean regardless of whether it is what is desired) or not, I would recommend using Option Explicit to enforce requiring variable declaration. You can have this automatically tacked in for any new projects by (in the VBE) going to Tools|Options|the 'Editor' tab and tick the 'Require Variable Declaration' check box. This will save you headaches later.

Hope that helps,

Mark

[Aaron719]

Wow... does exactly what I need. Thank you so much Mark and everyone else who looked at this for me. I'll have to do some research to figure out just exactly what this code is doing. I know a lot of what you wrote is new to me.

I just realized I can give rep on here. I will be sure to do that for everyone that helped...if I can. Really appreciate it.

Thanks again,
Aaron

[GTO]

... Thank you so much Mark and everyone else who looked at this for me. I'll have to do some research to figure out just exactly what this code is doing. I know a lot of what you wrote is new to me...

Hi Aaron,

You are of course most welcome, and I am happy that we were able to help. My compliments in that while I see (IMO) too many who simply are looking for a free bit of work to be done, it is plain to me that you are making 'nose to the grindstone' efforts in learning. That in my opinion, epitomizes the finest of this site. Here is the code (re)marked-up a bit.



Sub Ehour3()
Dim rngSearchRange As Range
Dim W As Range
Dim strFirstCellAddress As String
Dim strLastFoundAddress As String

'// As you already know, Set a reference to our non-contiguous range of interest. //
Set rngSearchRange = _
ThisWorkbook.Worksheets("Incomes").Range("G14:AK14,G30:AK30,G46:AK46,G62:AK62," & _
"G78:AK78,G94:AK94,G110:AK110," & _
"G126:AK126,G142:AK142,G158:AK158," & _
"G174:AK174,G190:AK190")

'// Make an initial attempt as finding the value we are looking for. By my memory //
'// (something you should not trust), if we are Looking In Values, we are looking in //
'// the cells (vs. looking in the Formula Bar). I did not really contemplate it (a //
'// bit lazy on my part) and do not know exactly why we apparently need to coerce what//
'// we are looking for into a string. The .Find Method of course is looking for a //
'// string, but normally, numbers are coerced by VBA. I would guess at the form- //
'// atting of the cells we are looking through. //
Set W = rngSearchRange.Find(What:=CStr(ThisWorkbook.Worksheets("Menu").Range("E2").Value), _
LookIn:=xlValues, _
SearchOrder:=xlByRows _
)

'// If we find it once, then switch gears into looping. //
If Not W Is Nothing Then

'// Record where we found first found the sought value, so we can tell when we find //
'// the same cell again. //
strFirstCellAddress = W.Address(0, 0, , -1)
W.Offset(-1).Value = ThisWorkbook.Worksheets("Menu").Range("G2").Value

Do
'// Unfortunately, I am about as hazy/tired as I write this, as when I wrote the //
'// code. I think it may be a waste and you can try ditching the second test. //
strLastFoundAddress = W.Address(0, 0, , -1)

'// For most of this, VBA Help will answer. FindNext fits in this. //
Set W = rngSearchRange.FindNext(W)

'// As mentioned, a bit overly cautious, but test to ensure we aren't finding a //
'// cell we already found. //
If Not W.Address(0, 0, , -1) = strFirstCellAddress _
And Not W.Address(0, 0, , -1) = strLastFoundAddress Then

'// Plunk in the value you want. //
W.Offset(-1).Value = ThisWorkbook.Worksheets("Menu").Range("G2").Value

End If

'// See when we need to exit the loop, by testing against the first cell's address //
Loop While Not W.Address(0, 0, , -1) = strFirstCellAddress
End If
End Sub

Hope that helps, and Happy Coding!

Mark