Fill Value Automatically

[premjeetgupta]

I have a workbook which consume lots of data. I need a macro code; if any column contains value "-40.0" in any sheet then 2 column of left side value should automatically "-40.0" (for fist left col.) & "0" (for second left col.). *macro should ignore first row as a header.

 

[Krishna Kinker Singh]

Dear Premjeet,

Can you please attached a sample file with the problem, so that it problem looks clear.

Thanks,

Krishna

 

[premjeetgupta]

Hi Krishna,

Thanks for response. Here is the attached a sample file. Where red marked column should be like green marked. Just for example.

 

[Krishna Kinker Singh]

Ok I am checking it, I will contact you once I get solution for this.

Thnaks,

Krishna

 

[Krishna Kinker Singh]

Dear Premjeet,

Please below is the code, if ther will be "-40.0" simultaneously in two column the this will be a problem, at least run code and check,





Private Sub Worksheet_Activate()
i = 2
j = 6

Do While Cells(i, j).Value <> ""

Cells(i, j).Select

Do While ActiveCell.Value <> ""
Cells(i, j).Select

If Cells(i, j).Value = "-40.0" Then
Cells(i, j - 1).Value = "-40.0"
Cells(i, j - 2).Value = "0.0"
End If
i = i + 1

Loop

j = j + 1
i = 2
Loop
End Sub



If it is not resolved yet then reply for your comments.

Thanks,

Krishna

 

[premjeetgupta]

Hi Krishna,

Code you have shared is running in only active sheet. And if i am trying to run with excel file which contains multiple sheet it works for only active sheet. also one more thing whenever i am coming on sheet where code applied from other sheet it automatically start running again & again.

 

[Krishna Kinker Singh]

Dear Premjeet,

Use below code you, need to paste this code in separate module in excel and run this function by pressing F5 only, workbook is also attached.


Sub WorksheetLoop()
Dim WS_Count As Integer
Dim K As Integer
WS_Count = ActiveWorkbook.Worksheets.Count
For K = 1 To WS_Count
ActiveWorkbook.Worksheets(K).Activate

I = 2
j = 6
Do While Cells(I, j).Value <> ""
Cells(I, j).Select
Do While ActiveCell.Value <> ""
Cells(I, j).Select
If Cells(I, j).Value = "-40.0" Then
Cells(I, j - 1).Value = "-40.0"
Cells(I, j - 2).Value = "0.0"
End If
I = I + 1
Loop
j = j + 1
I = 2
Loop
Next K
End Sub


Thanks,

Krishna