PDA

View Full Version : Operator <= doesn't seem to work!



EddieJar
09-19-2015, 11:11 AM
Hi folks, New to this forum, but there seems to be lots of folk about with loads of information. Maybe someone can help me: In my spreadsheet I have a column that deducts one date from anothe (=D4-B1) to give me a number.
n order to carry the task out I do an If Then statement, that should be carried out IF the value is less than, or equal to zero. However, although what I am trying to do works, It still carries out the task even if the value is greater than zero! Any help please?

Cheers

Eddie

mikerickson
09-19-2015, 12:12 PM
What is the code that isn't working? (I'm assuming this is in a VBA code. If its a worksheet formula, similarly what formula?)

EddieJar
09-19-2015, 02:20 PM
What is the code that isn't working? (I'm assuming this is in a VBA code. If its a worksheet formula, similarly what formula?)

Hi Mike .... Even if G4 is greater than 0, it still carries out the task - any ideas?

Dim DestRow As Long
Sheets("Transactions").Activate
Dim i As Long
i = 1
While Sheets(1).Cells(i, 2) <> ""
i = i + 1
Wend
DestRow = i
If G4 <= 0 Then
Worksheets("Transactions").Cells(DestRow, 1).Value = "Debit"

Worksheets("Transactions").Cells(DestRow, 3).Value = Worksheets _
("Payments In_Out").Cells(4, 1)

Worksheets("Transactions").Cells(DestRow, 2).Value = Worksheets _
("Payments In_Out").Cells(4, 4)

Worksheets("Transactions").Cells(DestRow, 5).Value = Worksheets _
("Payments In_Out").Cells(4, 3)

Sheets("Payments In_Out").Activate
Range("D4") = Range("D4") + 31



End If

Paul_Hossler
09-19-2015, 02:41 PM
If G4 refers to a worksheet cell, then



If Range("G4").Value <= 0 Then


should work

EddieJar
09-19-2015, 02:58 PM
If G4 refers to a worksheet cell, then



If Range("G4").Value <= 0 Then


should work

EddieJar
09-19-2015, 03:00 PM
Thanks Paul I will give that a try tomorrow.....bedtime here now!
Cheers Eddie

SamT
09-19-2015, 08:29 PM
This uses the same logic as your code, but in a different format. The only logical difference is that your code used the first empty cell in Column "B" as the DestRow and this uses the Row after the last used cell in Column "B"

Dim DestRow As Long
Dim PIO4 As Range
Set PIO4 = Sheets("Payments In_Out").Rows(4)

With Sheets("Transactions")
DestRow = .Cells(Rows.Count, "B").End(xlUp).Row + 1
If .Range("G4") <= 0 Then
.Cells(DestRow, "A").Value = "Debit"
.Cells(DestRow, "C").Value = PIO4.Columns(1)
.Cells(DestRow, "B").Value = PIO4.Columns(4)
.Cells(DestRow, "E").Value = PIO4.Columns(3)

With PIO4.Columns(4)
.Value = .Value + 31
End With
End If
End With

EddieJar
09-20-2015, 03:39 AM
This uses the same logic as your code, but in a different format. The only logical difference is that your code used the first empty cell in Column "B" as the DestRow and this uses the Row after the last used cell in Column "B"

Dim DestRow As Long
Dim PIO4 As Range
Set PIO4 = Sheets("Payments In_Out").Rows(4)

With Sheets("Transactions")
DestRow = .Cells(Rows.Count, "B").End(xlUp).Row + 1
If .Range("G4") <= 0 Then
.Cells(DestRow, "A").Value = "Debit"
.Cells(DestRow, "C").Value = PIO4.Columns(1)
.Cells(DestRow, "B").Value = PIO4.Columns(4)
.Cells(DestRow, "E").Value = PIO4.Columns(3)

With PIO4.Columns(4)
.Value = .Value + 31
End With
End If
End With

EddieJar
09-20-2015, 03:42 AM
Hi folks.. thanks to SamT and Paul.. Both of these solutions work brilliantly, however Sam, since I have to repeat this several times (and my 'duff' code is already written!), Paul's answer fits the bill as it is easier to change. Still bemused as to why I didn't spot the 'Range' solution earlier! Ah well, problem now solved. Thanks again for the interest and help.

Regards Eddie

SamT
09-20-2015, 06:20 AM
repeat this several times... is easier to change
Change in what manner?

EddieJar
09-20-2015, 10:05 AM
Hi Sam - well some items are credited (different column), some debited, as in the code, and others are randome. I have kept your code as it is obviously more efficient, to work on a better solution - If you don't mind that is!

Thanks again for the very helpful advice.

Cheers Eddie

SamT
09-20-2015, 11:06 AM
If the Columns for Credits and Debits are always the same for them respectively; Use two subs: GetCredits, and GetDebits.

I would have to see all the actual code to make really meaningful suggestion. Up to you

BTW, I believe that all offerings on VBAExpress are free to all, unless otherwise noted.