range offset

[tandavina]

I've this part of coding I don't quite understand. What does the If rngIDFind.Offset(, 12) & c.Offset(, 13) & c.Offset(, 14) = "" Then refers to?


For Each c In rngLook
Set rngIDFind = rngIDLook.Find(c)
If Not rngIDFind Is Nothing Then
If rngIDFind.Offset(, 12) & c.Offset(, 13) & c.Offset(, 14) = "" Then
Ws2.Cells(c.Row, "G") = 1
Application.CutCopyMode = False
End If

[XLGibbs]

I do believe I wrote that code over at Mr.Excel...it looks like mine anyway :)

here is the skinny:

For each c in rngLook

'this means, for each part of the object rngLook do the following actions. in this case, for each cell in the range (since cells are part of a range, sheets are part of workbook, workbook is part of workbooks...and so forth..)

Set rngIDFind = rngIDLook.Find(c)

create a new object reference by finding the value of the C (in this case the cell) in the object (in this case a Range of cells)

If Not rngIDFind is nothing then

Basically, if it finds something (NOT nothing) then do the following:

If rngIDFInd.Offset(,12) & c.Offset(,13) & c.Offset(,14)

says look combine the data inside cells 12, 13 ,14 columns to the right of the cell which was found. and actually, it looks like it should be :

rngIDFind.Offset(,12) & rngIDFind.Offset(,13) & rngIDFind.Offfset(,14) = ""

and if there is nothing (a string of "") then flag column G of the C.Row (For each C in rngLook) with a 1 by this:

ws2.cells(c.Row,"G") = 1


I am unsure with cutcopymode is set to false, I don;t remember the whole code I wrote, but at some point, something was pasted i guess...that line would just turn off the "ants" border around a selected area...

Hope that clears it up....but you may want to change the one line as I indicated above..

EDIT: If the intent is to look for nothing in the cells row, column 12, and the matching data's cell column 13, and 14...then don't change that one line...

[XLGibbs]

Inn fact here is the rest of the code I gave you as originally written with the requirements in comments. I changed one part, since it needs to look in columns N,O,P of the sheet1 for blanks if a matching ID is found...then put the 1 in column G of the match on sheet 2 (rngIDFind)

Hope this code does better...sorry about the minor oversight. this was like a week or two ago though...


Sub evaluateandcopy2()

Dim rngLOOK As Range, lRow As Long, c As Range, ws1 As Worksheet
Dim rngIDLook As Range, rngIDFind As Range
Application.ScreenUpdating = False
lRow = 7
Set Ws2 = Sheets("Sheet2"): Set ws1 = Sheets("Sheet1")
Set rngLOOK = Ws2.Range("N7:n200"): Set rngIDLook = ws1.Range("B:B")

'look in column N of Sheet2 (Ws2) get ID, search for that ID in column B of sheet1
' if found, look in N,O,P of sheet1. If all blank, put a 1 in G of Sheet2, same row
' as ID

For Each c In rngLOOK
Set rngIDFind = rgnIDLook.Find(c)
If Not rngIDFind Is Nothing Then
If c.Offset(, 12) & c.Offset(, 13) & c.Offset(, 14) = "" Then
Ws2.Cells(rngIDFind.row, "G") = 1
Application.CutCopyMode = False
End If
End If
Next c
Set ws1 = Nothing: Set rngLOOK = Nothing
Set c = Nothing: Set rngIDFind = Nothing: Set rngIDLook = Nothing
Application.ScreenUpdating = False
End Sub

[tandavina]

thanks. i actually have this:
Sub UpdateBlockTime()
Dim flag1, flag2, flag3, flag4 As Boolean
'Look for day in range
Dim rngLook1 As Range, c As Range, ws1 As Worksheet
Dim rngDayLook As Range, rngDayFind As Range
Application.ScreenUpdating = False
Set ws2 = Sheets("StudGrp_BU"): Set ws1 = Sheets("05_S2")
Set rngLook1 = ws2.Range(",5"): Set rngDayLook = ws1.Range("B8:B34")

'Look for start time in range
Dim rngLook2 As Range, d As Range
Dim rngsTimeLook As Range, rngsTimeFind As Range
Set rngLook2 = ws2.Range("C6:CN6"): Set rngsTimeLook = ws1.Range("C8:C34")

'Look for end time in range
Dim rngLook3 As Range, e As Range
Dim rngeTimeLook As Range, rngeTimeFind As Range
Set rngLook3 = ws2.Range("C7:CN7"): Set rngeTimeLook = ws1.Range("D8:D34")

'Look for student grp in range
Dim rngLook4 As Range, f As Range
Dim rngStudGpLook As Range, rngStudGpFind As Range
Set rngLook4 = ws2.Range("B25:B63"): Set rngStudGpLook = ws1.Range("E8:F34")

For Each c In rngDayLook
Set rngDayFind = rngDayLook.Find(c)
If Not rngDayFind Is Nothing Then
ws2.cells(c.Row
End Sub



which i do not really know how to continue. what i need here is to update the block time sheet (StudGrp_BU) against the complementary studies schedule sheet (05_S2). i've attached a sample of my workbook for clearer view.

[XLGibbs]

Well, hey, at least my code finds it's way around, i guess that is worth something. let me snoop around your file, see what i can whip up to help you out. will post back shortly!

:whistle:

[XLGibbs]

Okay, had a look, and I cannot figure out what you were getting ready to do with that code.

How exactly does the StudGru sheet get updated with the complimentary studies data...perhaps you could show a sample result?

or explain what is supposed to go where, and ith what conditions?

[tandavina]

this is the sample of the desired result. as you can see in StudGrp_BU, the time slots that are blocked are the times of the CS modules for each respective Student Group.

[XLGibbs]

Okay, caught me a little late tonight (after midnight, gotta work in AM). I will sniff out a solution for you tomorrow after work, provided no one else has come to the rescue before then! Once i have a look at the sample results to determine what needs to be done, i may have more questions.

[tandavina]

alright, thanks. be waiting for your solution. :thumb

[tandavina]

alright, i've actually tried out this but not sure what is wrong.


Sub UpdateFacBlockTime()
Dim rngLook5 As Range, g As Range
Dim rngFacCodeLook As Range, rngFacCodeFind As Range
Application.ScreenUpdating = False
Set ws2 = Sheets("Facility_BU"): Set ws1 = Sheets("05_S2")
Set rngLook5 = ws2.Range("B9:B134"): Set rngFacCodeLook = ws1.Range("L8:L34")

Dim rngLook6 As Range, h As Range
Dim rngFacDayLook As Range, rngFacDayFind As Range
Set rngLook6 = ws2.Range("C5:CN5"): Set rngFacDayLook = ws1.Range("B8:B34")

Dim rngLook7 As Range, i As Range
Dim rngFacSTimeLook As Range, rngFacSTimeFind As Range
Set rngLook7 = ws2.Range("C6:CN6"): Set rngFacSTimeLook = ws1.Range("C8:C34")

Dim rngLook8 As Range, j As Range
Dim rngFacETimeLook As Range, rngFacETimeFind As Range
Set rngLook8 = ws2.Range("C7:CN7"): Set rngFacETimeLook = ws1.Range("D8:D34")


For Each g In rngLook5
Set rngFacCodeFind = rngFacCodeLook.Find(g)
If Not rngFacCodeFind.Value(g) = rngFacCodeLook.Value(g) Then

For Each h In rngLook6
Set rngFacDayFind = rngFacDayLook.Find(h)
If rngFacDayFind.Value(h) = rngFacDayLook.Value(h) Then

For Each i In rngLook7
For Each j In rngLook8
Set rngFacSTimeFind = rngFacSTimeLook.Find(i)
Set rngFacETimeFind = sngFacETimeLook.Find(j)
If rngFacSTimeFind.Value(i) = rngFacSTimeLook.Value(i) And rngFacETimeFind.Value(j) = rngFacETimeLook.Value(j) Then
ws2.Cells(g.Row, j.Column) = 1
ws2.Cells(g.Row, i.Column) = 1
End If
Next j, i
End If
Next h
End If
Next g
End Sub


like the previous one, this is to update facility block time. the venues are not set in 05_S2 yet but i've put in one ("P4-TR11") to try out the codings.

[XLGibbs]

Hiya Tandevina, I have been severely hampered handling a major piece of damage control for a project at work, and unfortunately have not taken a good look at your request yet. My apologies.

It would be helpful if you identified what part of the code you posted is not doing correctly...

Is it not doing anything? Is there an error? Is it placing data in the wrong place..

Sometimes it is extremely useful to use F8 and step through the code line by line, using your cursor to scroll over variables to see if they are populating correctly etc...you can also use F9 to set a Break Point in the code, where it will run normally up until that line, then pause so you can either run normal from there, or use the F8 step through...

[tandavina]

thanks.

it shows a "Object variable or With block variable not set"

[XLGibbs]

Well, the code is basically saying

Set (Empty Object) = (an object to be found)

While you have evaluations for the found objects being equal, you do not account for the possibility that there is no match in the (object to be found) part of the SET statement

You can combat this by (more) IF statements to skip the conditional test if there is not match....I know it is a pain, but a necessary evil. Let me poke around your source and result files previously supplied and see what I can come up with while I have some coffee buzz still kicking.

Set rngFacCodeFind = rngFacCodeLook.Find(g)
If not rngFaceCodeFind is nothing then
If Not rngFacCodeFind.Value(g) = _
rngFacCodeLook.Value(g) Then
'''rest of code
End If

[Zack Barresse]

thanks.

it shows a "Object variable or With block variable not set"
Don't trust this error, it can be quite erroneous. In fact, you can receive this error if you do not have a closing Select statement, If statement, Next statement or With statement. So be sure and check ALL of your closing tags.

[XLGibbs]

Tandavina:

I am looking at your proposed sample, and the desired result. In the sample there are already similar looking data that appears to get either moved or replaced by the desired result. Is the actual begining point a blank slate where you have all 1s?

I just want to know if part of the requirement is to clear the old values first before running the matching sequence.

[rbrhodes]

cross post.

http://www.ozgrid.com/forum/showthread.php?p=229991#post229991

[XLGibbs]

Well, I don;t mean to be disrespectful, but Jindon at Ozgrig has done some fine work on this already. I am all for helping and such, but it is best to post issues with his code back to him on that thread.

I love to code, I love to help people, and I love solving problems like this. I hate when that time is wasted.

As in Jindon's questions at Ozgrid, it is still extremely unclear exactly what logic is applied to set the 1's into the time slots.

The data does not correspond, nor do the resulting values, and the data is either incomplete, or you must have multiple tables to compare and feed values into the desired resulting grid.

The program1 , program2 etc are within a much longer string, and identifying which one of those to match use for the row of the grid is not clear at all. as there are multiple matches for each case using the described contiion in the cross post.

I apologize, but I am not going to continue this thread, since Jindon has already invested considerable effort into formulating solutoins that appear to be effective based on the information provided. he has offered to finish the code, which he had tested to satisfaction pending further explanation of the criteria.

It was fun trying to work it out, and I am happy to say that my code looks almost like Jindons. Good luck, and no hard feelings. Time spent on this total since replying is about 2.5 hours. :hi:

[tandavina]

clearing of old values is not the requirement here. requirement here is to updating the existing with the 05_S2 worksheet.

[jindon]

clearing of old values is not the requirement here. requirement here is to updating the existing with the 05_S2 worksheet.
Go to some another planet!

You didn't even provide enough data and logic to us.

[XLGibbs]

clearing of old values is not the requirement here. requirement here is to updating the existing with the 05_S2 worksheet.

Unfortunately, the information and logic to achieve this was not provided to either of the volunteers who were offering their valuable time to you.

Please read the posting guidelines at this forum and others. Cross posting is a sure way to avoid getting the help you may need,as we are all volunteers, and it is unfair to others who need assistance to have multiple volunteers concurrently working on a problem. Particularly with different sets of information which makes it even harder for you as the user, since you would be getting a lot of mixed information.