You are here:

Excel/Thanks again. All is fine.

Advertisement


Question
Image with questions
Image with questions  
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,


Sub replacedata()
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
  Else
     'what do you want to do if "F3" value is
     ' not found
  End If
End If
Next
End Sub

'=======================================

Sub ProcessQuestionMark()
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
 End If
Next
End If
End Sub

These worked for me or at least as I expected based on your picture and description.

--
Regards,
Tom Ogilvy


 

---------- 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

Answer
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.

--
Regards,
Tom Ogilvy

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.