Macro To Copy Value Within Empty Cell

[pencari]

Thx before for chandoo forum

can someone help me with vba code

i am not good at english, so i give an excel example

i need to copy and paste value, but im stuck with the empty cell

thx again

 

[Hui]

H4: =IF(J4="","AAA",IF($D$3="A","AAA",IF($D$3="B","BBB","")))
Copy H4 down

 

[pencari]

H4: =IF(J4="","AAA",IF($D$3="A","AAA",IF($D$3="B","BBB","")))
Copy H4 down

thx for the reply Sir Hui

but it is not right,

if i change the d4 to B, The AAA Remain AAA, Not changed to BBB

 

[Hui]

I assume you mean you changed D3 not D4?

It works fine for me
Did you copy H4 down?

 

[pencari]

I assume you mean you changed D3 not D4?

It works fine for me
Did you copy H4 down?

Sorry, yeah D3

i have make the new file

sorry for the bad english sir hui


i think it need a macro

 

[PCosta87]

Hi @pencari

Maybe something like this:

Sub test()

    Dim c, rng As Range
    Dim lastrow, max As Integer

    max = Application.WorksheetFunction.max(Columns("J"))
    lastrow = Columns("J").Cells(Rows.Count).End(xlUp).Row
    Set rng = Range("H4:H" & lastrow)
    
    For Each c In rng.Cells
        If c.Offset(0, 2).Value = max Then
            c.FormulaLocal = "=REPT($D$3,3)"
            c.Value = c.Value
        End If
    Next c

End Sub

When you add 2 to J and change D3 to "B", run macro and you get D3 repeated 3 times "BBB"
If you then change D3 to "C" and add 3 to J, run the macro and you get "CCC" and so on

 

[pencari]

Hi @pencari

Maybe something like this:

Sub test()

    Dim c, rng As Range
    Dim lastrow, max As Integer

    max = Application.WorksheetFunction.max(Columns("J"))
    lastrow = Columns("J").Cells(Rows.Count).End(xlUp).Row
    Set rng = Range("H4:H" & lastrow)
   
    For Each c In rng.Cells
        If c.Offset(0, 2).Value = max Then
            c.FormulaLocal = "=REPT($D$3,3)"
            c.Value = c.Value
        End If
    Next c

End Sub

When you add 2 to J and change D3 to "B", run macro and you get D3 repeated 3 times "BBB"
If you then change D3 to "C" and add 3 to J, run the macro and you get "CCC" and so on
View attachment 36345

thx for the response,

its near what i mean

but i dont want the formula rept, i just need the formula at h4:h86 and check at j4:j86


i tried to delete the "c.FormulaLocal = "=REPT($D$3,3)"

but its cant

 

[PCosta87]

@pencari

If you are planning to have 1,2,3,4,5... in column J it is not a good ideia to use IF's as you need to keep adding conditions to accommodate the new entries
In this case you can run the code below after adding the new number to column "J" and before changing "D3"... so if you were to add "2" to column "J", run the code after adding "2" and then change "D3" to B. This time i had the macro leave the formula in the cells.
You can change the formula if you wish.

Sub test()

    Dim c, rng As Range
    Dim lastrow, max As Integer

    max = Application.WorksheetFunction.max(Columns("J"))
    lastrow = 86
    Set rng = Range("H4:H" & lastrow)
  
    For Each c In rng.Cells
        c.Value = c.Value
      
        If c.Offset(0, 2).Value = max Or c.Offset(0, 2).Value = "" Then
            c.FormulaLocal = "=repetir($D$3;3)"
        End If
    Next c

End Sub

 

[pencari]

@pencari

If you are planning to have 1,2,3,4,5... in column J it is not a good ideia to use IF's as you need to keep adding conditions to accommodate the new entries
In this case you can run the code below after adding the new number to column "J" and before changing "D3"... so if you were to add "2" to column "J", run the code after adding "2" and then change "D3" to B. This time i had the macro leave the formula in the cells.
You can change the formula if you wish.

Sub test()

    Dim c, rng As Range
    Dim lastrow, max As Integer

    max = Application.WorksheetFunction.max(Columns("J"))
    lastrow = 86
    Set rng = Range("H4:H" & lastrow)
 
    For Each c In rng.Cells
        c.Value = c.Value
     
        If c.Offset(0, 2).Value = max Or c.Offset(0, 2).Value = "" Then
            c.FormulaLocal = "=repetir($D$3;3)"
        End If
    Next c

End Sub

sorry for my bad english and bad example,

coulum j just for checking if there is a value or no, no matter what is the value, and the D3 Just have A or B, N coulum H just AAA and BBB, its not mean to have 3 letter from D3, its just for checking,
if D3 = A, when i input something at J4:j10, the H4:H10 coulum change to AAA, But when in the middle operation, I want to change D3 to B, I dont want the H4:H10 coulum which has been AAA to change to BBB, and when i input something at Coulum j11, the H11 must Be BBB, Cause it still use the formula

sorry for causing inconvenience sir

 

[PCosta87]

Hi @pencari

Here you go... run macro to convert to values the contents of "H" which have something in J. The rest remains with formula and will show result when something is entered in J

 

[pencari]

Hi @pencari

Here you go... run macro to convert to values the contents of "H" which have something in J. The rest remains with formula and will show result when something is entered in J

yes its correct

this helped me alot

thanks sir Pcosta87

 

[PCosta87]

@pencari

You are welcome