Search column; copy contents/paste special

[stags81]

Hello,

I'm using Excel 2007, and I'm looking to create a macro that searches P4:P542 for all values which include an integer greater or less than (but not equal to) zero. For each value it finds, I would like it to copy and paste special the contents into the cell below it. I'm fairly new at Excel VBA and writing code, so unfortunately I don't really know where to even begin. Can you help?

Thanks!

Mike

 

[John Davis]

Hello,

I'm using Excel 2007, and I'm looking to create a macro that searches P4:P542 for all values which include an integer greater or less than (but not equal to) zero. For each value it finds, I would like it to copy and paste special the contents into the cell below it. I'm fairly new at Excel VBA and writing code, so unfortunately I don't really know where to even begin. Can you help?

Thanks!

Mike

Maybe, this can help you to get started.

Sub stags81()
Dim i As Long

For i = 542 To 4 Step -1

    If Range("P" & i).Value <> 0 And Range("P" & i).Value = Int(Range("P" & i)) Then Range("P" & i).Offset(1).Value = Range("P" & i).Value

Next i

End Sub

 

[stags81]

Hi John,

Thanks so much! Works great!

I'd like to add one more element to this...for each cell that the macro pastes values into, i'd like to select an option from a drop down box that is in the cell immediately to the left. For example, if the macro pastes a value in P170, the drop down box is in O170. All of column O has drop down boxes, and there is only one option for each box (a check mark).

Is this possible? Thanks again for your help!

Mike

 

[John Davis]

Hi John,

Thanks so much! Works great!

I'd like to add one more element to this...for each cell that the macro pastes values into, i'd like to select an option from a drop down box that is in the cell immediately to the left. For example, if the macro pastes a value in P170, the drop down box is in O170. All of column O has drop down boxes, and there is only one option for each box (a check mark).

Is this possible? Thanks again for your help!

Mike

Mike:

More than likely it's doable, but I've never used drop down boxes. I'm sure that someone one the board can provide further assistance.

 

[xenou]

Are these drop boxes from the forms menu or the activex menu? I prefer the former. If so, all you need to do is write the cell value to the cell (I think):

Like so, Where X is the value you want to write:
(untested - could be wrong!)

Sub stags81()
Dim i As Long

For i = 542 To 4 Step -1
    If Range("P" & i).Value <> 0 And Range("P" & i).Value = Int(Range("P" & i)) Then 
        Range("P" & i).Offset(1).Value = Range("P" & i).Value
        [COLOR="Blue"]Range("O" & i).Offset(1).Value = [/COLOR][COLOR="Green"][B]X[/B][/COLOR]
    End If
Next i

End Sub

 

[stags81]

Hi,

I meant drop down boxes made by means of data validation...not sure if we're referring to the same thing? The only option in the box is a u with a diaeresis (two dots on top) that appears as a check mark.

Thanks again!

Mike

 

[xenou]

I see, well the thing to do would be to write that character to the cell. Dunno what actually the character is though! You could copy it also.

It may be a special font (for instance, "a" in Marlett font is a check mark). Or it may have been inserted from the symbol character map.

 

[stags81]

Upon running the macro John started us with, I'm getting a mismatch error:

"Run-time error '13': Type mismatch"

I'm assuming that the macro starts at the bottom and works its way up...maybe it doesn't know what to do if the value in column P = 0? I would like to have the macro just skip that record, however I'm having trouble trying to word it correctly.

Thank you all!

Mike