Excel Basics

[SilverFreak]

I need to write a macro to work by tomorrow morning, so I'd appreciate any help.

I'm trying to use an IF....ELSE statement but I can't seem to get it to work.

I want the macro to check if a cell, say (A2), has a "v" in it. If it doesn't then I want it to show a default value. If the cell has a "v" in it then I want it to copy cell (A15) and paste it into (A2).

Then I want the whole operation to move to Column 3. eg, Cell (B2) and (B15).

I tried to find a thread with this sort of thing on it, but I haven't got much time!!!

Cheers

SilverFreak

 

[SkipVought]

SilverFreak,

for col = 1 to 2
  with cells(2, col)
    if .value like "*v*" then
      .value = defaultvalue
    else
      .value = cells(15, col).value
    end if
  end with
next

Skip,
[sub]
[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it! [/sub]

 

[000Steve000]

If I understand the requirement, like this.

Sub Mysub()

LastCol = 10
For MyCol = 1 To LastCol
    If Cells(2, MyCol) = "v" Then
        Cells(2, MyCol) = Cells(15, MyCol)
    End If
Next MyCol

End Sub

 

[SilverFreak]

Hi,

That seems to work fine, but I forgot to mention that I have a range of cells to scan whether they have "v" in. And then move along one column, and then another column. etc

Thanks

 

[SkipVought]

...I have a range of cells to scan...[/b]
Please be more specific. My crystal ball is foggy.

Skip,
[sub]
[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it! [/sub]

 

[SilverFreak]

Sorry, From Cell (A9) to (A13), Cell (B9) to (B13) etc.

 

[SkipVought]

for col = 1 to 2
  for row = 9 to 13
    with cells(row, col)
      if .value like "*v*" then
        .value = defaultvalue
      else
        .value = cells(15, col).value
      end if
    end with
  next
next

Skip,
[sub]
[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it! [/sub]

 

[SilverFreak]

Hi,
Thanks to you both. It works exactly how I needed it to.

Cheers

SilverFreak