adding text to row a b and c while looping

I assume you want to write values to specific cells on the "NOK IA1" worksheet? The current process pastes the information in the target workbook on the current lastrow + 1 (determined separately for both column E and E). So if for eample you would like to write something to columns A and B you could insert the following code just before the Next i statement

' insert this row along with the other declarations
    dim LastNOKRow as Long

' write to column A and B
    LastNOKRow=ms.cells(rows.count,"A").End(xlup).Row +1
    ms.cells(LastNOKRow,"A").Value = "Something into column A"
    ms.cells(LastNOKRow,"B").Value = "Something into column B"

or alternatively

ms.Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = "something in column A"
ms.Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Value = "something in column B"

I assume you want to write values to specific cells on the "NOK IA1" worksheet? The current process pastes the information in the target workbook on the current lastrow + 1 (determined separately for both column E and E). So if for eample you would like to write something to columns A and B you could insert the following code just before the Next i statement

' insert this row along with the other declarations
    dim LastNOKRow as Long

' write to column A and B
    LastNOKRow=ms.cells(rows.count,"A").End(xlup).Row +1
    ms.cells(LastNOKRow,"A").Value = "Something into column A"
    ms.cells(LastNOKRow,"B").Value = "Something into column B"

or alternatively

ms.Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = "something in column A"
ms.Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Value = "something in column B"

Try replacing

     If UCase$(.Cells(i, AG).Value) = "Gross NAV" And UCase$(.Cells(i, C).Value) = "NOK/IA1" Then
          .Cells(i, "AG").Offset(1).Resize(1).Copy
          MyData = Format(ms.Range("E" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial(Transpose:=True), Percent)
     End If

by

     If UCase$(.Cells(i, AG).Value) = "Gross NAV" And UCase$(.Cells(i, C).Value) = "NOK/IA1" Then
          ms.Range("E" & Rows.Count).End(xlUp).Offset(1, 0).Value = .Cells(i, "AI").Value
     End If

I found your problem. replace the code with

If UCase$(.Cells(i, "AG").Value) = "Gross NAV" And UCase$(.Cells(i, "C").Value) = "NOK/IA1" Then
          ms.Range("E" & Rows.Count).End(xlUp).Offset(1, 0).Value = Format(.Cells(i, "AI").Value,Percent)
     End If

You forgot the quotes around the column names AG and C, and I forgot the Format as percent. Because you have on error resume next, it skipped your IF statement and always conducted the update.

I think this may the code you are looking for

Sub Gross_NAV()
On Error Resume Next

'#
'# declare
'#
     Dim lngRowNumber As Long
     Dim xlsTarget As Excel.Worksheet
     Dim lngTargetRow As Long
    
'#
'# block application interface updating for performance reasons
'#
      Application.ScreenUpdating = False
      
'#
'# initialise
'#
     Set xlsTarget = Workbooks("Book4.xlsx").Worksheets("NOK IA1")
     
'#
'# process all rows from the allocation worksheet
'#
     With Thiswork.Worksheets("Allocation")
          For lngRowNumber = 1 To .UsedRange.Rows.Count
          
          '#
          '# get the last row on the target worksheet and add 1 count in case
          '# new values are to be written
          '#
               lngTargetRow = xlsTarget.UsedRange.Rows.Count + 1
               
          '#
          '# if the value if column1 is a valid date, copy the date to the ms worksheet
          '#
               If IsDate(.Cells(lngRowNumber, 1).Value) Then
                    .Cells(lngTargetRow, "D").Value = .Cells(lngRowNumber, "A").Value
                    .Cells(lngTargetRow, "D").NumberFormat = "dd/mm/yyyy"
               End If
               
          '#
          '# if  column C holds NOK/IA1 then the value of column AI should be copied into column E on the target worksheet
          '#
               If UCase$(.Cells(i, C).Value) = "NOK/IA1" Then
                    .Cells(lngTargetRow, "E").Value = Format(.Cells(lngRowNumber, "AI").Value, Percent)
               End If
               
          Next lngRowNumber
     End With
     
'#
'# restore application user interface
'#
     Application.ScreenUpdating = True

End Sub

Sorry, few typing errors

try below code

Sub Gross_NAV()
On Error Resume Next

'#
'# declare
'#
     Dim lngRowNumber As Long
     Dim xlsTarget As Excel.Worksheet
     Dim lngTargetRow As Long
    
'#
'# block application interface updating for performance reasons
'#
      Application.ScreenUpdating = False
      
'#
'# initialise
'#
     Set xlsTarget = Workbooks("Book4.xlsx").Worksheets("NOK IA1")
     
'#
'# process all rows from the allocation worksheet
'#
     With ThisWorkbook.Worksheets("Allocation")
          For lngRowNumber = 1 To .UsedRange.Rows.Count
          
          '#
          '# get the last row on the target worksheet and add 1 count in case
          '# new values are to be written
          '#
               lngTargetRow = xlsTarget.UsedRange.Rows.Count + 1
               
          '#
          '# if the value if column1 is a valid date, copy the date to the ms worksheet
          '#
               If IsDate(.Cells(lngRowNumber, 1).Value) Then
                    .Cells(lngTargetRow, "D").Value = .Cells(lngRowNumber, "A").Value
                    .Cells(lngTargetRow, "D").NumberFormat = "dd/mm/yyyy"
               End If
               
          '#
          '# if column C holds NOK/IA1 then the value of column AI should be copied into column E on the target worksheet
          '#
               If UCase$(.Cells(lngRowNumber, "C").Value) = "NOK/IA1" Then
                    .Cells(lngTargetRow, "AI").Value = Format(.Cells(lngRowNumber, "A").Value, "Percent")
               End If
               
          Next lngRowNumber
     End With
     
'#
'# restore application user interface
'#
     Application.ScreenUpdating = True

End Sub

Last attempt. Try the below code

Sub Gross_NAV()
On Error Resume Next

'#
'# declare
'#
     Dim lngRowNumber As Long
     Dim xlsTarget As Excel.Worksheet
     Dim lngTargetRow As Long
    
'#
'# block application interface updating for performance reasons
'#
      Application.ScreenUpdating = False
      
'#
'# initialise
'#
     Set xlsTarget = Workbooks("Book4.xlsx").Worksheets("NOK IA1")
     
'#
'# process all rows from the allocation worksheet, when column C holds the value NOK/IA1
'# copy information to the target worksheet
'#
     With ThisWorkbook.Worksheets("Allocation")
          For lngRowNumber = 1 To .UsedRange.Rows.Count
               If UCase$(.Cells(lngRowNumber, "C").Value) = "NOK/IA1" Then
                    lngTargetRow = xlsTarget.UsedRange.Rows.Count + 1
                    .Cells(lngTargetRow, "D").Value = .Cells(1, 1).Value
                    .Cells(lngTargetRow, "D").NumberFormat = "dd/mm/yyyy"
                    .Cells(lngTargetRow, "E").Value = Format(.Cells(lngRowNumber, "AI").Value, "Percent")
               End If
          Next lngRowNumber
     End With
     
'#
'# restore application user interface
'#
     Application.ScreenUpdating = True

End Sub

If this does not work you will have to post copies of your workbooks.

I will look at it tommorow. Is book4 open while you are running the code or are you assuming this code will open and save the workbook as well?

I tried to have a look, but I am not able to download the file as I get a password protected message

Ok. I have renamed the target workbook to Allocation, as that makes more sense to me. When you run the macro it will prompt you to select the FND November workbook. It will loop through all records identifying the sections (starting with a date) and for the records in that section will search for NOK/IA1 in column C. When found, it will add a record to the Allocation worksheet with the date in column A and the value of column AI in column B. Feel free to change this to meet you needs. Note that in the example provided column AI contain the value ZERO for every NOK/IA1 record.

I have looked at the workbooks provided and the description of your requirements, and they are quite confusing. For example column AG does not hold the description Gross NAV at all, column AI is supposed to be formatted as a percentage, but in fact contains an amount. The date you want to copy is not stored on the row itself, but at the beginning of a section. etc etc

- Do you want to copy information from "book1" (worksheet Sheet1) to "FND November" (worksheet Allocation)?
- which columns do you want to copy and where should the copied information be placed on the target worksheet?
- When do you want to copy the information (i.e. the condition)

I have looked at the workbooks provided and the description of your requirements, and they are quite confusing. For example column AG does not hold the description Gross NAV at all, column AI is supposed to be formatted as a percentage, but in fact contains an amount. The date you want to copy is not stored on the row itself, but at the beginning of a section. etc etc

- Do you want to copy information from "book1" (worksheet Sheet1) to "FND November" (worksheet Allocation)?
- which columns do you want to copy and where should the copied information be placed on the target worksheet?
- When do you want to copy the information (i.e. the condition)

Glad to hear.

Will you please remember to mark this thread as SOLVED, and click on the star icon if you are happy with my contribution. Thank you.

pls open a new thread for this request as it is not allowed (forum rules) to raise a new request in an existing thread. When you do raise a new thread, please ensure that your request is clearly formulated.