Excel/Thanks again. All is fine.
QUESTION: Hi Tom,
Still chipping away at my music program which is coming together very nicely in no small part due to you being there as a life saver to this semi-novice. I'm still working with Microsoft Office Excel 2007. I'm sure these Macro questions are probably so easy it's not going to be much fun for you. But some months from now (probably) I'll have one that you may find really interesting.
Anyway, if you have the time (my God with well over 16,000 questions already answered. they should have a higher honor for you than just MVP) perhaps sainthood should be considered.
Have a great day! Barry
ANSWER: Hello Barry,
Dim sh1 As Worksheet, sh2 As Worksheet
Dim r1 As Range, r2 As Range, cell1 As Range
Dim r2A As Range
Dim res As Variant
Set sh1 = Worksheets("Start")
Set sh2 = Worksheets("Midi Note #")
Set r1 = sh1.Range("F3", sh1.Cells(sh1.Rows.Count, "F").End(xlUp))
Set r2 = sh2.Range("A1", sh2.Cells(sh2.Rows.Count, "A").End(xlUp))
For Each cell1 In r1
If Application.CountIf(r2, cell1) > 0 Then
res = Application.Match(cell1, r2, 0)
If Not IsError(res) Then
Set r2A = r2(res)
r2A.Offset(0, 1).Copy cell1
'what do you want to do if "F3" value is
' not found
Dim sh1 As Worksheet
Dim r1 As Range, r1A As Range, cell As Range
Set sh1 = Worksheets("Data") '<=== change name of sheet to your Sheet name
Set r1 = sh1.Range("A1", sh1.Cells(sh1.Rows.Count, "A").End(xlUp))
On Error Resume Next
Set r1A = r1.SpecialCells(xlConstants, xlTextValues)
On Error GoTo 0
If Not r1A Is Nothing Then
For Each cell In r1A
If Trim(cell.Value) = "?" Then
cell.Offset(0, 3).Value = 0
These worked for me or at least as I expected based on your picture and description.
---------- FOLLOW-UP ----------
QUESTION: Hi Tom,
Thanks so much. You fixed it all. I don't know how I got things jumbled up. I'm always careful to do a cut and paste of anything you send me. In this one instance I had decided to make a copy of your macro and carefully paste it into a Microsoft program titled Office One Note.
I then used that copy as the basis for entering in your solutions. I will have to check it out. I suspect One Note generated a slightly altered version of your copy and that may have caused the problem. Perhaps the program captured it with a different default font. I'll let you know if I solved the mystery. It certainly was surprising.
Anyway I was certainly right to send on a workbook copy of my finished version. Doing that at least could clearly alert you to the madness that had entered into you error free macros. Cheers again. Barry
I suspected you had pasted it somewhere other than into the code module. I was thinking some type of non-printing character(s) was/were added. I don't think Font has an effect mainly because in the VBE, I could find no place where you could edit the font or alter it.