Finding duplicates and replacing.

[aqualiary]

Hi for example, this is my data

1 - - + + -
2 + + + - -
3 - - - - +
3 + + - - +

What i'm trying to do if a given row has at least 3 negative values, to replace all the negative values of the row to string "A" (for example). Only if a given row has 3 or more negative values, eg row 2 has only 2 negative values so I can just leave the row alone. (also all the negative values are the same, and all positive values are the same number and they are given as a string not an integer)

How do I go about doing this using IF statements, without worksheetfunction. (we were told to code it without using worksheetfunction)

[ashleyuk1984]

You could use COUNTIF to count the number of negative values
Like this:



This is a good starting point, to get coding vba for it

[david000]

Sub MinusStr()

Dim rng As Range

Dim cel As Range

Dim c As Range

For Each cel In Range("a1:a4")

If Application.WorksheetFunction. CountIf(Range(cel, cel.Offset(, 3)), "<0") >= 3 Then

Set rng = cel.Resize(, 4)

For Each c In rng

If c.Value = <0 Then c.Value = "A"

Next c

End If

Next cel

End Sub

I tried not to use the WorksheetFunction

Maybe you can settle for a B

[Kenneth Hobs]

Sound like a homework question. If you are limited in tools, make your own. The tool used to make such is Function(). To ask homework questions, ask in concepts like: How do I iterate a range of cell values in one row and all rows? That is what learning is about, conceptual thinking, not just details.

The FAQ is:
Can I ask about my homework here?

Please don't ask us questions directly out of your coursework materials. We are happy to provide direction and guidance for those studying VBA and other software. Be open about the fact that it is coursework, and you'll likely find yourself with more resources than you could possible need.