Oryos
01-26-2016, 02:19 PM
hi everyone,
I'm working on a new macro here to create a table that is used to give me better overview of some important documents.
I have a table with three rows with 1. Country, 2. amount and 3. documents as hearder.
it looks like this:
Country
amount
documents
Country A
100
A
100
B
country B
...
...
This list goes on for many countries and the values within the row 'amount' are variable meaning that every day I have to put in new data. The rest stays the same.
What I want now is to create a new table with a better overview of documents needed. For example document A might be necessary for 2 or 3 countries and in that new table the document A should only be mentioned once.
Please find attached my file for better understanding of the issue described and my final outcome.
I was trying to put this into an VBA code and this is what i got so far:
Sub run_all()
Dim countryl1 As Long, result1 As String, i As Long
Range("A45:A52").Clear
country1 = Range("B2") - Range("B3").Value
country2 = Range("B4") - Range("B5").Value
For i = 45 To 52
If country1 >= 1 Then
result1 = Range("C2")
result2 = Range("C3")
If country2 >= 1 Then
result3 = Range("C5")15296
Else
result1 = ""
End If
Range("A45") = result1
Range("A46") = result2
If Range("A46") >= 1 Then
Range("A47") = result3
End If
End If
Next i
End Sub
my thinking was the following:
IF there is a value of any kind in cell B2 or B3 then write the respective documents found in Cell C2 and C3 in Cell A45. If there is no value in Cell B2 or B3 than leave it blank and move on to check B4 and B5 of values and so on.
The tricky part for me here is this:
1. My code doesnt represent the case when nothing is found for country 1
2. my code doesnt cover the situation that one file has already been found and it doesnt need to be written into my new table.
3. one row to the right of my final outcome there should be an x depending on the document type cause only some documents are necessary to print (see my attached file). How can I implement that?
Is there anybody who could help me here? Any advice is much appreciated!
Thank you
I'm working on a new macro here to create a table that is used to give me better overview of some important documents.
I have a table with three rows with 1. Country, 2. amount and 3. documents as hearder.
it looks like this:
Country
amount
documents
Country A
100
A
100
B
country B
...
...
This list goes on for many countries and the values within the row 'amount' are variable meaning that every day I have to put in new data. The rest stays the same.
What I want now is to create a new table with a better overview of documents needed. For example document A might be necessary for 2 or 3 countries and in that new table the document A should only be mentioned once.
Please find attached my file for better understanding of the issue described and my final outcome.
I was trying to put this into an VBA code and this is what i got so far:
Sub run_all()
Dim countryl1 As Long, result1 As String, i As Long
Range("A45:A52").Clear
country1 = Range("B2") - Range("B3").Value
country2 = Range("B4") - Range("B5").Value
For i = 45 To 52
If country1 >= 1 Then
result1 = Range("C2")
result2 = Range("C3")
If country2 >= 1 Then
result3 = Range("C5")15296
Else
result1 = ""
End If
Range("A45") = result1
Range("A46") = result2
If Range("A46") >= 1 Then
Range("A47") = result3
End If
End If
Next i
End Sub
my thinking was the following:
IF there is a value of any kind in cell B2 or B3 then write the respective documents found in Cell C2 and C3 in Cell A45. If there is no value in Cell B2 or B3 than leave it blank and move on to check B4 and B5 of values and so on.
The tricky part for me here is this:
1. My code doesnt represent the case when nothing is found for country 1
2. my code doesnt cover the situation that one file has already been found and it doesnt need to be written into my new table.
3. one row to the right of my final outcome there should be an x depending on the document type cause only some documents are necessary to print (see my attached file). How can I implement that?
Is there anybody who could help me here? Any advice is much appreciated!
Thank you