Macro Checking / Changing data in excel

[darinmc]

A B C D
64 1 12 £6.10
64 1 45 £5.52
64 1 4 £7.20
64 2 6 £5.52
64 3 6 £5.52
125 1 55 £5.52


I have about 300 lines of the above data, I would like a Macro that will CHECK for 2 conditions.
All values in column B = 1
All values in column C > 37.5

It should then create a new row (preferably straight after the row that needs changing) keeping amount in C as 37.5 and remaining balance in the new line as well as changing the value from a 1 to a 2.


A B C D
64 1 12 £6.10
64 1 37.5 £5.52
64 2 7.5 £5.52
64 1 4 £7.20
64 3 6 £5.52
64 4 6 £5.52
125 1 37.5 £5.52
125 2 17.5 £5.52

Hope ur able to help...

Thx
Darin

 

[rmikesmith]

Darin,

What have you attempted so far?


Regards,
Mike

 

[SkipVought]

Hi,

This could be done, I believe, with a Union Query. faq68-5829.

Query 1 for column C <= 37.5 (You did not specity a heading. A table ought to have a row of headings)

Query 2 for column C > 37.5.
[tt]
Select A, B, C, D
From [Sheet1$]
Where C <= 37.5
UNION
Select A, 2, C, D
From [Sheet1$]
Where C > 37.5
[/tt]
or whatever your sheet name is and whatever the column headings are.


Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[darinmc]

Hi to both
I am just using the columns as headings... I have NO idea what i should be doing, so giving me the code would be greatly appreciated or sendind me to a link of similar requirements.

I do want it to be in MACRO format, and as stated originaly, there are 2 conditions...
Col C is > 37.5
and
Col B = 1

My sql is not too bad BUT have not really done that much VBA...

I thought my original question was fairly clear and the 2nd set of data shows exactly how i need the results...

This is all to do with Payroll and the data will later be imported into sage...

Thx
Darin

 

[rmikesmith]

Darin,

Something like the following should work for you:

Sub AdjustData()
Dim LastUsedRow As Long
Dim i As Long
Dim j As Long

   Application.ScreenUpdating = False
   With Worksheets("Sheet1")
     LastUsedRow = .Cells(65536, 1).End(xlUp).Row
     If LastUsedRow > 1 Then
       i = 2
       Do
         If .Cells(i, 2).Value = 1 And .Cells(i, 3) > 37.5 Then
           j = i + 1
           .Cells(j, 1).EntireRow.Insert
           .Cells(j, 1).Value = .Cells(i, 1).Value
           .Cells(j, 2).Value = 2
           .Cells(j, 3).Value = .Cells(i, 3).Value - 37.5
           .Cells(i, 3) = 37.5
           .Cells(j, 4).Value = .Cells(i, 4).Value
           i = j
           LastUsedRow = LastUsedRow + 1
         End If
         i = i + 1
       Loop Until i > LastUsedRow
     End If
   End With
   Application.ScreenUpdating = True
End Sub

Assumptions:
1. Data resides on a worksheet named 'Sheet1'; change to your actual sheetname.
2. Data resides in columns A-D, as you indicated.
3. Data begins in Row 2 with headings in Row 1 (this allows an exit from the macro if no data present)


Regards,
Mike

 

[darinmc]

Thx for the previous help, its taken me a while to try get the next part written out.

1) BUILDING ONTO THE 1st MACRO.. would it be possible in the 1st macro created, to fill the rows affected with the colour yellow? This will help me for the following macros
..................................................

2) SECOND MACRO

Example 2 – Testing for Values = 37.5[/COLOR RED]
A B C D
64 1 37.5 £5.35
64 2 5 £5.35
64 1 5 £5.60
64 1 5 £5.80

Where
Col A = Col A (i.e. The employee number is equal)
AND
Col B = 1
AND
Col C = 37.5

THEN RESULTS =
A B C D
64 1 37.5 £5.35
64 2 5 £5.35
64 11 5 £5.60 (Just changing the Number in Col B and Keeping Col D)
64 12 5 £5.80 (the same values)

--------------------------------------------------------------------------------

3rd MACRO - Maybe finish this later if toooo complicated?
Example 3 – Tesing for values < 37.5
Where
Col A = Col A (i.e. The employee number is equal)
AND
Col B = 1
AND
Col C = 37.5

There might be 1 or 2 or 3 rows, I’m not sure if this complicates it more?
…1 Row… (already taken care of in 1st Macro)
A B C D
64 1 5 £5.35 (If Col C < 37.5, then NO CHANGE, this was already done using the 1st Macro)

…2 Rows Totalling <= 37.5 …
A B C D
64 1 5 £5.35
64 1 25 £5.60 (If SUM of Col C < 37.5, Then ONLY change 1 row in Col B to 6)
RESULT = :
A B C D
64 1 5 £5.35
64 6 25 £5.60 (Just changed to 6)

2 Rows, totalling more than 37.5
A B C D
64 1 15 £5.35
64 1 25 £5.60 (If SUM of Col C < 37.5, Then ONLY change 1 row in Col B to 6)
RESULT = :
A B C D
64 6 12.5 £5.35
64 1 25 £5.60
64 11 2.5 £5.35
(Keeps the row with HIGHEST Val col D, Splits the other row with 15, Total of Col B 1 & 6 = 37.5 and the rest into 11)




Thx, I know this is a lot to ask, I was finding it hard to even ask the question

Darin